Removing duplicate items within a category

[Mtnbiker]

Hi,

I have a table which appears below:

ITEM PRICE DATE
Paper clips 0.89 2/2/06
Paper clips 1.29 2/12/07
Stapler 2.57 12/29/05
Stapler 2.99 1/5/07

How do I write a query which will show the most recent cost for each item? I
am hoping for a result as shown below:

ITEM PRICE DATE
Paper clips 1.29 2/12/07
Stapler 2.99 1/5/07

Thanks in advance for your help!

 

[John Spencer]

One way

SELECT ITEM, PRICE, [Date]
FROM [Yourtable]
WHERE [YourTable].[Date] =
(SELECT Max([Date])
FROM [YourTable] as Temp
WHERE Temp.Item = [YourTable].Item

If that is too slow, post back with a copy of the SQL (View: SQL) and I'll
suggest a faster but more complex alternative.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..