Get most recent record based on 3 different fields

[benignor]

Hi,

I have a table similar to the following:

PatientID | LabID | LabDate | Result
001 | 55 | 01jan14 | 9.5
001 | 55 | 01feb14 | 10.0
001 | 55 | 01mar14 | 8.7
001 | 66 | 30jan14 | 11.2
001 | 66 | 30feb14 | 15.4
001 | 66 | 30mar14 | 13.0

002 | 55 | 01jan14 | 12.1
002 | 55 | 01feb14 | 9.9
002 | 55 | 01mar14 | 14.5
002 | 66 | 30jan14 | 16.5
002 | 66 | 30feb14 | 13.0
002 | 66 | 30mar14 | 10.0

Using a single-step Access query, I need to retrieve, for each PatientID, the most recent LabDate and Result *of a given LabID*. Thus, from the example dataset above, the desired output for LabID 55 is:

PatientID | LabID | LabDate | Result
001 | 55 | 01mar14 | 8.7
002 | 55 | 01mar14 | 14.5

I have searched this forum and others, but have not found an answer that I can directly tanslate to my situation. I have successfully written queries (with included subqueries) that retrieve the most recent of all the Labs, but have failed at obtaining a result dataset that contains only the records within a specified LabID.

For example, the query below fails because whenever the most recent of *all* the LabDates is not the same as the most recent of *the LabDates with a LabID=55*, the correct record is not included in the results. In the example dataset above, 0 records are returned.

SELECT a.PatientID, a.LabID, a.LabDate, a.Result
FROM Labs AS a
INNER JOIN (SELECT PatientID, MAX(LabDate) AS MaxLabDate FROM Labs GROUP BY PatientID) AS b
ON (a.PatientID = b.PatientID) AND (a.LabDate = b.MaxLabDate)
WHERE (((a.LabID)=55));

Any help would be greatly appreciated!

 

[JHB]

Try the below:

SELECT a.PatientID, a.LabID, a.LabDate, a.Result
FROM Labs AS a INNER JOIN (SELECT PatientID, MAX(LabDate) AS MaxLabDate, labID FROM Labs GROUP BY PatientID, labID)  AS b ON (a.labID = b.labID) AND (a.LabDate = b.MaxLabDate) AND (a.PatientID = b.PatientID)
WHERE (((a.LabID)=55));

 

[benignor]

That worked beautifully, thank you so much! I also understand the reasoning for this solution, the key being that the inner join include the LabID. I was trying to add WHERE statements to both table aliases separately and getting nowhere.

 

[JHB]

You're welcome, luck with your project.