return only last records query

A

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
 
K

KARL DEWEY

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

Anne

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

KARL DEWEY said:
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


Anne said:
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
Click to expand...
Click to expand...
 
K

Klatuu

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


KARL DEWEY said:
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


Anne said:
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
Click to expand...
Click to expand...
 
Top