Select ONE record

[Ira]

I have a table with: ID, PurchaseDate, Price

with data like:

1,3/2/10,$500
2,4/5/10,$240
3,1/2/10,$120
2,12/31/10,$130
4,3/24/10,$90
1,4/5/10,$25
1,5/6/10,$35

I would like to return the MOST RECENT PURCHASE for Each ID. Can
anyone help?

Thanks

 

[John W. Vinson]

I have a table with: ID, PurchaseDate, Price

with data like:

1,3/2/10,$500
2,4/5/10,$240
3,1/2/10,$120
2,12/31/10,$130
4,3/24/10,$90
1,4/5/10,$25
1,5/6/10,$35

I would like to return the MOST RECENT PURCHASE for Each ID. Can
anyone help?

Thanks

A Subquery: put a criterion on PurchaseDate of

=(SELECT Max(X.PurchaseDate) FROM yourtable AS X WHERE X.ID = yourtable.ID)

Note that if you have ties (two purchases on the same date) you'll see both.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com