To retrived a unique items only but… 2

[Goondu]

This is a sample of a Table called ‘StockItem’

ItemNum UnitPrice SupplyDate
0001 5.00 12-Dec-2007
0001 12.00 10-Jul-2007
0002 7.00 12-Dec-2007
0003 2.00 12-Dec-2007
0004 3.00 12-Dec-2007
0004 5.00 10-Jul-2007

I need to retrieve the rows of ItemNum base on the last ‘SupplyDate’. I am having problems excluding rows with the same ‘ItemNum’, example.

ItemNum UnitPrice SupplyDate
0001 12.00 10-Jul-2007
0002 7.00 12-Dec-2007
0003 2.00 12-Dec-2007
0004 5.00 10-Jul-2007

Due to the UnitPrice difference, I need to pick only one from the ItemNum base on the SupplyDate.

Anyone can help? Version 2000.

Thanks.

 

[dhookom]

Try a query like:

SELECT *
FROM StockItem
WHERE SupplyDate = (SELECT Max(SupplyDate) FROM StockItem s WHERE S.ItemNum = StockItem.ItemNum);

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Another way:
SELECT A.*
FROM StockItem INNER JOIN (
SELECT ItemNum, Max(SupplyDate) AS LastDate FROM StockItem GROUP BY ItemNum
) AS B ON A.ItemNum = B.ItemNum AND A.SupplyDate = B.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Goondu]

Thank you, Duane

It works. Had to use the Min() function to retrive older records.

Thank you, PHV

Had to edit a little to get it right.

SELECT A.*
FROM StockItem A INNER JOIN (
SELECT ItemNum, Min(SupplyDate) AS LastDate FROM StockItem B GROUP BY ItemNum
) AS B ON A.ItemNum = B.ItemNum AND A.SupplyDate = B.LastDate;

Thanks for helping me to get around this problem.