return only last records query

[Anne]

Hello! I have a table that has county information by year. I would like to
write a query that returns only the latest year for each county. I have used
the LAST function in my total selection, which puts the latest year first in
what is returned, but it also returns the other years as well (in order). I
would really like to see just the latest year for each county, not all of the
records.
For example, I have Anderson County, years 2001, 2002, 2003, 2004, 2005. I
also have Davidson County, years 2001, 2002, 2003, 2004, as well and I want
to see Anderson County's 2005 record and Davidson County's 2004 record.
I am using Access 2007.
Thanks
Anne

 

[KARL DEWEY]

Try this --
SELECT [YourTable].*
FROM [YourTable]
WHERE ((([YourTable].[Year])=(SELECT Max([Year]) FROM [YourTable] as
Temp WHERE Temp.[County]= [YourTable].[County])));

 

[Anne]

Thanks, Karl, that's just what we were looking for!!!!!!!
Anne

Try this --
SELECT [YourTable].*
FROM [YourTable]
WHERE ((([YourTable].[Year])=(SELECT Max([Year]) FROM [YourTable] as
Temp WHERE Temp.[County]= [YourTable].[County])));

--
KARL DEWEY
Build a little - Test a little

Hello! I have a table that has county information by year. I would like to
write a query that returns only the latest year for each county. I have used
the LAST function in my total selection, which puts the latest year first in
what is returned, but it also returns the other years as well (in order). I
would really like to see just the latest year for each county, not all of the
records.
For example, I have Anderson County, years 2001, 2002, 2003, 2004, 2005. I
also have Davidson County, years 2001, 2002, 2003, 2004, as well and I want
to see Anderson County's 2005 record and Davidson County's 2004 record.
I am using Access 2007.
Thanks
Anne

 

[Klatuu]

Nicely done, Karl
--
Dave Hargis, Microsoft Access MVP

Try this --
SELECT [YourTable].*
FROM [YourTable]
WHERE ((([YourTable].[Year])=(SELECT Max([Year]) FROM [YourTable] as
Temp WHERE Temp.[County]= [YourTable].[County])));

--
KARL DEWEY
Build a little - Test a little

Hello! I have a table that has county information by year. I would like to
write a query that returns only the latest year for each county. I have used
the LAST function in my total selection, which puts the latest year first in
what is returned, but it also returns the other years as well (in order). I
would really like to see just the latest year for each county, not all of the
records.
For example, I have Anderson County, years 2001, 2002, 2003, 2004, 2005. I
also have Davidson County, years 2001, 2002, 2003, 2004, as well and I want
to see Anderson County's 2005 record and Davidson County's 2004 record.
I am using Access 2007.
Thanks
Anne