Retaining the most recent value associated with multiple dates

[reddy]

I am trying to build the query logic for the following:

Month ActivityName Standard
0308 Data Entry 50
0408 Data Entry 100
0508 Data Entry 100
0408 MailSort 200
0508 Mailsort 250
0308 Counting 2000
0408 ADE 500

I want the query to return the most recent standard associated with multiple
dates. Not all actvities were present in all three months (0308,0408, 0508).
Some were present only in 0308 or only in 0408.

The results of the query should be:
0508 Data Entry 100
0508 Mailsort 250
0308 Counting 2000
0408 ADE 500

Please advise. Thanks!

 

[John Spencer]

One method would be to use a subquery in the FROM clause.

SELECT YourTable.Month, ActivityName, Standard
FROM YourTable INNER JOIN
(SELECT ActivityName, Max(YourTable.Month) as TheLatest
FROM YourTable
GROUP BY ActivityName) as Tmp
ON YourTable.ActivityName = Tmp.ActivityName
AND YourTable.Month = Tmp.TheLatest

Another would be to use a subquery in a where clause

SELECT YourTable.Month, ActivityName, Standard
FROM YourTable
WHERE YourTable.Month =
(SELECT MAX(tmp.Month)
FROM YourTable as Tmp
WHERE Tmp.ActivityName = YourTable.ActivityName)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County