Need most current date

[Pamula]

Using Access 2002.

I have a table that shows each time an item was ordered along with the date it was ordered and the cost. Generically, it looks like this:

Item # Date ordered Cost
000455 1/1/2006 45.67
000455 2/15/2006 45.67
000455 10/20/2006 49.00
000567 4/16/2006 256.00
000567 9/15/2006 260.00

So, for each item there may be anywhere from 1-50 different lines with different dates/prices. I am trying to create a query that will pull the most current date and cost. So the query results would be:

Item # Date ordered Cost
000455 10/20/2006 49.00
000567 9/15/2006 260.00

I thought I read something about a 'last' function, but can't find any more info -- like how to use it. Is there any way to get Access to pull the most current date in a query?

 

[PHV]

A starting point (SQL code):
SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT [Item #], Max([Date Ordered]) AS LastDate
FROM yourTable GROUP BY [Item #]
) AS L ON A.[Item #] = L.[Item #] AND A.[Date Ordered] = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pamula]

I am receiving a syntax error in the From clause. Here is the actual SQL Code:

SELECT A.* FROM [2010 Medtronic SD] AS A INNER JOIN
(SELECT [ITEM #], Max([PO-DATE]) AS LastDate
FROM 2010 Medtronic SD GROUP BY [ITEM #])
AS L ON (A.[ITEM #] = L.[ITEM #]) AND ( A.[PO DATE] = L.LastDate)

Any ideas?

 

[PHV]

FROM [!][[/!]2010 Medtronic SD[!]][/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pamula]

Thanks. I see that now. It works GREAT! Thank you very much!!