Aggregate query - Displaying field associated with MAX record

G

Guest

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top