Query for most recent date for each test for ID on form

[capndave]

I have a command button on a form that I want to run a query to return the most recent lab result for each type of test only for the MRN matching the current form frmPtReg.

The tblLabResult on which to run the query has the following:
tblLabResult
RecNum MRN AcctNum LabDate LabTest LabRes
1 3232323 7878788 5/27/2010 K 4.2
2 3232323 7878788 5/27/2010 HDL 170
3 3232323 7878788 5/27/2010 LDL 2005
4 3232323 7878788 5/28/2010 HDL 323
20 11111 222111 7/29/2010 BNP 23
21 11111 222111 7/29/2010 K 3.5
22 11111 222111 7/29/2010 CHOL 270
23 11111 222111 7/29/2010 HDL 200
24 11111 222111 7/29/2010 TRO 2.8
25 11111 222111 7/30/2010 TRO 2.1

I either get no records returned or records with the most recent results for all MRNs.
If the current frmPtReg has MRN of 3232323 I only want the records 1,3,4 returned with all fields but RecNum

Any help would be greatly appreciated- my deadline is close!
Thanks, CapnDave

 

[traingamer]

There are a number of ways. One way is to use a nested group by query to return only those records that meet your criteria (the latest results), then only use thos records. (Tested)

SELECT TblLabResult.RecNum, TblLabResult.MRN, TblLabResult.AcctNum, TblLabResult.LabDate, TblLabResult.LabTest, TblLabResult.LabRes
FROM TblLabResult
WHERE (((TblLabResult.RecNum) In 

[blue](SELECT Last(TblLabResult.RecNum) AS LastOfRecNum
FROM TblLabResult
GROUP BY TblLabResult.MRN, TblLabResult.AcctNum, TblLabResult.LabTest
ORDER BY Max(TblLabResult.LabDate);))[/blue]

AND ((TblLabResult.MRN)=3232323))
ORDER BY TblLabResult.LabDate;

You probably would want to change my hardcoded

((TblLabResult.MRN)=3232323))

to something like

((TblLabResult.MRN)=[blue] [Forms]![frmPtReg]![YourFieldName].[Value] [/blue]))

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[capndave]

Thanks Traingamer- one question- does your solution depend on the recnum reflecting the date. ie, the higher the recnum the more recent the date

If so, this is not the case. I'm not sure why I included a recnum in the table, other than my understanding is it is good practice

Thanks for your time,
CapnDave

 

[traingamer]

Thanks Traingamer- one question- does your solution depend on the recnum reflecting the date. ie, the higher the recnum the more recent the date

Yes. And it will fail if a higher recnum is on an earlier date - let me revisit this and get back to you.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

One way (tested - with additional recnums out of sequence) is to group the results based on the latest date in an external query (here named qryGroupLab)

SELECT TblLabResult.MRN, TblLabResult.AcctNum, TblLabResult.LabTest, Max(TblLabResult.LabDate) AS MaxOfLabDate
FROM TblLabResult
GROUP BY TblLabResult.MRN, TblLabResult.AcctNum, TblLabResult.LabTest;

then join it in the main query

SELECT TblLabResult.RecNum, TblLabResult.MRN, TblLabResult.AcctNum, TblLabResult.LabDate, TblLabResult.LabTest, TblLabResult.LabRes

FROM TblLabResult INNER JOIN qryGroupLab gl ON (TblLabResult.LabDate = gl.MaxOfLabDate) AND (TblLabResult.LabTest = gl.LabTest) AND (TblLabResult.AcctNum = gl.AcctNum) AND (TblLabResult.MRN = gl.MRN)

WHERE (((TblLabResult.MRN)=3232323));

Your results will be much, much faster if you move MRN part of the where clause to qryGroupLab. This was just for testing.



Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard