Possible to use DMax for "groups" of records within the same table???

[DKO]

This is hard to explain - so I can only hope that someone understands and knows a solution...

I want to update a "JOB" field in table "PERSON" to a field from a source file "JOBS".

The tables are linked via a unique id for each person, "SSN"

The problem is that each PERSON may have multiple records in the "JOBS" table, and I only want to select the record with the most recent JOB.

There is another field in the JOBS table that contains the "JOB DATE"

For example:

SSN.........JOB...........JOB_DATE
123456789.........2A................1/1/2000
123456789.........3A.................2/2/2008
987654321..........4A.................1/1/2003
987654321...........5A.................3/3/2005

For each "SSN", I want to select the "JOB" that has the most recent "JOB DATE".

I'm completely stumped and feeling a little stupid at the moment...

My first instinct was to use the DMax function as a criteria on the DATE column, but I can't think of a way to do get the DMax for each SSN instead of the whole column....

Would appreciate any inpunt.

 

[namliam]

Use 2 queries:
1)
Select SSN , Max(Jobdate) from yourtable
2)
add your query and your table to the query, join them on SSN and (max)jobdate

Run query #2, have your result!

 

[DKO]

Use 2 queries:
1)
Select SSN , Max(Jobdate) from yourtable
2)
add your query and your table to the query, join them on SSN and (max)jobdate

Run query #2, have your result!

Cheers! I never even thought of using Grouping/Max. And I even used the word "group" in my title. LOL

Probably because I never needed it before.

I love this forum!