Query help - pulling field associated with max value from another field

[cardgage]

Good morning everyone,



I'm sure this question has been asked before, but I was unable to find an answer while searching these forums. I have two tables in my database - one that holds patient data (tblPatient) and another that stored medications for each patient (tblMeds); these are joined by the common field PatientID. Here is the structure of the tables:

tblPatient		tblMeds
PatientID - AutoNumber		MedID - AutoNumber
LastName - Text		PatientID - Number
FirstName - Text		Medication - Lookup
BirthDate - Date/Time		MedDate - Date/Time

The "Medication" field in tblMeds uses a lookup table to select between 5 different medications. I'm trying to create a query that pulls the "Medication" associated with the Max value in the "MedDate" field for each patient. When I create a query to pull the Max "MedDate" and PatientID, everything works fine. However, once I try to pull in "Medication" into this query I get the Max date for each Medication (i.e., multiple medications for each patient).

I would like the query to only pull the last medication that was entered for the patient based on the Max of "MedDate" for each "PatientID." Any help with this would be greatly appreciated!

[JoeM]

This can be done in a series of two queries:

1. Using tblMeds, create an Aggregate Query that pulls the Max MedDate for each PatientID. This query should only have the PatientID field and MedDate fields. You will be Grouping By PatientID and taking the Maximum of the MedDate field.

2. Now, create a new query joining the Query you just created above with tblMeds, joining on BOTH the PatientID and MedDate fields. Now, you can return whatever fields you want from tblMeds, and it will only return the records from the Maximum MedDate for each Patient.

[cardgage]

Awesome, thanks...worked like a charm!