Last record of each group of records

[jalexm]

[tt]Hello,
I have a table Tab1:

Name Year Value
-----------------------
John 2010 1000
Charles 2008 500
Charles 2001 800
John 2003 600
Mary 2012 2500

And need to get the following results:

Name Year Value
-----------------------
Charles 2008 500
John 2010 1000
Mary 2012 2500

As you can see, for each name I need to get the last record of the year.
Does anyone know how to get this?
Thanks.[/tt]

 

[dhookom]

The easiest method is to create a totals query that groups by Name and Max(Year). Then join this query to your data on the Name and Year fields.

This can also be done in a single query with a subquery.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

SELECT A.Name,A.Year,A.Value
FROM Tab1 A INNER JOIN (
SELECT [Name],Max([Year]) AS LastYear FROM Tab1
) B ON A.Name=B.Name AND A.Year=B.LastYear

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