MS Access - Find latest date

  • Thread starter Thread starter Todd Anderson
  • Start date Start date
T

Todd Anderson

I have a table which in simple terms has multiple items
that have multiple events associated with multiple dates,
looks something like this:

Item 1 Date1 Event1
Item 1 Date2 Event2
Item 1 Date3 Event3
Item 2 Date2 Event4
Item 2 Date4 Event2
Item 3 Date5 Event1

What I want to do is report on (hopefully through a
query?) the current status of each item, so I need to be
able to say for "Item 1, what is the latest event entry,
by date", "For Item 2, what is the latest event", etc..
So the resultant query or report for the example table
should look like this (assuming Date1 is earliest date,
and Date5 is latest date):
Item1 Date3 Event3
Item2 Date4 Event2
Item3 Date5 Event1

Can someone help?
 
I think that something like the following should work.

SELECT ITEM, Date, Event
FROM Table
WHERE Date =
(SELECT Max(T.Date)
FROM Table As T
WHERE T.Item = Table.Item)
 
Back
Top