Selecting ONLY the most recent record...

B

Brad Pears

I have a situation where management wants a report that lists notes for
customers - but they only want the MOST RECENT note for each customer to
show...

Customers and Notes are in a one-many relationship linked by field
CustomerID

I know there is a "select FIRST" SQL clause but it does not appear to work
in Access 2000 (what my project is based on)

What would be a good way to go about obtaining the results I am looking for?

Thanks, Brad

Thanks, Brad
 
R

Rick Brandt

Brad said:
I have a situation where management wants a report that lists notes
for customers - but they only want the MOST RECENT note for each
customer to show...

Customers and Notes are in a one-many relationship linked by field
CustomerID

I know there is a "select FIRST" SQL clause but it does not appear to
work in Access 2000 (what my project is based on)

What would be a good way to go about obtaining the results I am
looking for?
Click to expand...

Does the Notes table include a date field indicating when the record was
created? You need either that or an incrementing field per record or else
there is no way to determine which record is the most recent. There is
nothing that automatically keeps track of entry orderin a database.
 
B

Brad Pears

Yes there is a datestamp field in the notes table so I can order by that
field in descending order...

Brad
 
R

Rick Brandt

Brad said:
Yes there is a datestamp field in the notes table so I can order by
that field in descending order...
Click to expand...

Then use the query...

SELECT TOP 1 *
FROM TableName
ORDER BY DateField DESC
 
B

Brad Pears

What I really want is the "top 1" for each unique customer ID in the notes
table.

The query you gave me returns only one row for all the notes that are in
there. I need the most recent note for each customer.

Can this be maybe done somehow in a nested query??

Note: in the Notes table, there are multiple notes for each customer.

Thanks, Brad
 
Top