Aggregate query - Displaying field associated with MAX record

[ThomasTobey]

Well, someone finally stumped me on a query. ;-)
There is a table with thousands of records (tbl_Names):
SSN (String)
FullName (String)
DateLastUsed (Date)

Here are some sample values like the ones I'm dealing with:
SSN FullName MostRecentDateUsed
123-45-6789 Mouse, Mickey 1/15/2002
123-45-6789 Horse, Mickey 6/5/2004
456-77-8899 Duck, Daffney 1/4/2004
456-77-8899 Pyle, Daffney 6/1/2001
987-65-4321 Smith, Louise 9/8/2000
987-65-4321 Dunlap, Louise 5/4/1999
668-55-4444 Gardner Jr., Mike 4/2/2004
668-55-4444 Gardner, Michael 6/2/1998

I'm trying to tweak an aggregate query to pull SSN and the correct name (the
correct one is the most recent). As soon as I try to display the name in the
query, it kicks out both names. Using FIRST or LAST in the query is not
consistently correct (Example below):
SELECT tbl_Names.SSN, Max(tbl_Names.MostRecentDateUsed) AS
MaxOfMostRecentDateUsed, First(tbl_Names.FullName) AS FirstOfFullName FROM
tbl_Names GROUP BY tbl_Names.SSN;
I tried using FIRST after sorting the table via a subquery (assume the
source table cannot be touched / sorted), but it was like it ignored the
subquery sorts. It seems there needs to be another aggregate type, where if a
MAX is used in the query you can select a field associated in the same row as
that MAX value.
Any suggestions?
Thanks!
-Thomas

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com) is a small sample database
called "MaxQueryProblem.mdb" which illustrates the problem and two different
solutions.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[ThomasTobey]

Thanks! Both of these methods work great. I guess I was determined to get the
aggregate version to work for some reason. Perhaps it would be nice if
Microsoft added another option (other than first, last) to display a field
related to another chosen aggregate (the corresponding field to a max).
In any case, this does the job I need it to do.
Thanks again!
-Thomas

 

[Roger Carlson]

I've often thought that. It makes so much sense, but no version of SQL that
I know of has it. The reason I made the sample is because the question
comes up so often in newsgroups.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L