Querying/Finding the LAST status entry in a related table (1 Viewer)

[vangogh228]

I have a master table of client information with a key field of SSN. In a related table of status change entries, I have the STATUS field, which may have entries like "Active," "Current," "Inactive," "Retired," or "Deceased," ((entered with a date field also)) and related through the SSN field.

I need to be able to query the current status for the client. In other words, I may query on all "Active" clients. To do that, I would need to be able to find those clients whose last entry is "Active."

I have no idea how to write the query to find the last related entry for each master record. ANY HELP is appreciated.

Thanks!! Tom

 

[CJBIRKIN]

What you could do is use a group by query.

Group the clients and then select MAX on the date field this would give you the last entry for each client, i.e their current status, then you could use the criteria "Active" to select the active records

Chris

 

[vangogh228]

Chris:

Thanks. I tried it, as you suggested. I put 'max' as the Group By criterion in my date field, but I just get all my entries. I also tried 'last' as the criterion, but the same thing...

I wrote the query so that I would have MaxofDate and then based a report on the query, grouping by the Client Number. But, I still get all the entries and not just the last one.

The fields I am including in my query, from my status entry table, are

SSN
Date
Status

Thanks again!! Tom

 

[RichMorrison]

Tom,

It's a 2-step process.

Step one;
make a GroupBy query where you select SSN and Max([Date]).
Save as "query1"

Step two:
make a second query that joins query1 to your table on SSN and Date. Select SSN and Date from query1 and Status from the table.

RichM

 

[vangogh228]

AHA!!!!!

Got it... works great!! I think I getthe logic, too. Only include those necessary fields for the grouping in the first query... and the fields of related info in the second with the query!!

Thanks SO MUCH!!

This goes in my Hall of Fame file!!

Tom