Query that selects lab results based on date of labs relative to another lab

[majahops]

I have a pretty simple query that I wish to create, but I just haven't been able to do it. I'm really hoping that somebody can help.

I'm replacing the names of the tests that I actually want here, for security purposes... but let's suppose this is the scenario.



I'm analyzing patient's who have had a test called "PROCALCITONIN" performed. I only want to see the result for the first PROCALCITONIN test that a patient had performed. I also want to select the HEMOGLOBIN and GFR that the patient had most recently preceeding their first PROCALCITONIN test.

To make things easier, all of my information is in a single table. So let's suppose I have the following table with only 1 patient (of course, my actual table has thousands of patients) with the following information:

The first table is what I am starting with and the second table is the table I'd like to create with a query:

Patient ID	Test Name	Test Value	Test Date
1	Procalcitonin	4	1/1/2013
1	Hemoglobin	11.3	12/30/2012
1	Procalcitonin	3.6	1/4/2013
1	GFR	60	1/3/2013
1	GFR	47	12/30/2012
1	GFR	58.3	12/26/2012
1	Hemoglobin	11.8	12/22/2012
1	Hemoglobin	11.2	1/7/2013
1	GFR	60	1/2/2013
1	Hemoglobin	11.8	12/28/2012
Patient ID	Test Name	Test Value	Test Date
1	Procalcitonin	4	1/1/2013
1	GFR	47	12/30/2012
1	Hemoglobin	11.3	12/30/2012

Any information that anybody might be able to give me on how to pull this off would mean the world to me! Thanks so much!

[June7]

Criteria that must consider values of data in other records of same table is never easy and your criteria is especially complicated. Requires a unique ID field - autonumber type will serve. Try:

Query1: MinDateProcal
SELECT Table1.[Patient ID], Table1.[Test Name], Min(Table1.[Test Date]) AS [MinOfTest Date]
FROM Table1
WHERE (((Table1.[Test Name])="Procalcitonin"))
GROUP BY Table1.[Patient ID], Table1.[Test Name];

Query2: TestsProcalMinDate
SELECT Table1.ID, Table1.[Patient ID], Table1.[Test Name], Table1.[Test Value], Table1.[Test Date], MinDateProcal.[MinOfTest Date]
FROM MinDateProcal RIGHT JOIN Table1 ON MinDateProcal.[Patient ID] = Table1.[Patient ID]
WHERE (((Table1.[Test Date])<=[MinOfTest Date]));

Query3:
SELECT * FROM TestsProcalMinDate WHERE ID IN (SELECT TOP 1 Dupe.ID FROM TestsProcalMinDate As Dupe WHERE Dupe.[Patient ID]=TestsProcalMinDate.[Patient ID] AND Dupe.[Test Name]=TestsProcalMinDate.[Test Name] AND Dupe.[Test Date]<=TestsProcalMinDate.[MinOfTest Date] Order BY Dupe.[Test Date] DESC);


I don't see any reason to actually create second table. Just use the final query for whatever you intended to do with the table.

[orange]

I agree with June7 based on the data you have shown.
If you have a lot more data, different patients and different tests, then I would suggest

3 tables

tblPatient --->tblPatientTest<---tblTest
Patient Info     PatientTest          list of tests and descriptions
                     PaitientID
                     TestID
                     testDate
                     testValue

where tblPatienttest is a Junction table

[majahops]

June7, first off, thank you so much for your response. I did have one problem though. When I tried to create the second query, I got the following error:

"The specific field 'Table1.ID' could refer to more than one table listed in the FROM clause of your SQL statement"

Any idea where we may have gone wrong here?

Thank you again!!!

Criteria that must consider values of data in other records of same table is never easy and your criteria is especially complicated. Requires a unique ID field - autonumber type will serve. Try:

Query1: MinDateProcal
SELECT Table1.[Patient ID], Table1.[Test Name], Min(Table1.[Test Date]) AS [MinOfTest Date]
FROM Table1
WHERE (((Table1.[Test Name])="Procalcitonin"))
GROUP BY Table1.[Patient ID], Table1.[Test Name];

Query2: TestsProcalMinDate
SELECT Table1.ID, Table1.[Patient ID], Table1.[Test Name], Table1.[Test Value], Table1.[Test Date], MinDateProcal.[MinOfTest Date]
FROM MinDateProcal RIGHT JOIN Table1 ON MinDateProcal.[Patient ID] = Table1.[Patient ID]
WHERE (((Table1.[Test Date])<=[MinOfTest Date]));

Query3:
SELECT * FROM TestsProcalMinDate WHERE ID IN (SELECT TOP 1 Dupe.ID FROM TestsProcalMinDate As Dupe WHERE Dupe.[Patient ID]=TestsProcalMinDate.[Patient ID] AND Dupe.[Test Name]=TestsProcalMinDate.[Test Name] AND Dupe.[Test Date]<=TestsProcalMinDate.[MinOfTest Date] Order BY Dupe.[Test Date] DESC);


I don't see any reason to actually create second table. Just use the final query for whatever you intended to do with the table.

[majahops]

Nevermind, I hadn't created an autonumber ID that's why that didn't work. So the second query worked. Trying the third query now. Thanks so much again!!!

[majahops]

June7,

So the first two queries worked great. In the second query, I only have lab values that preceed (or are equal to) the date for my earliest Procalcitonin. That is SO great. The thing is, when I try to run the third query, the system just hangs and hangs and hangs.

Any ideas? I'm so close thanks to you!!!

Criteria that must consider values of data in other records of same table is never easy and your criteria is especially complicated. Requires a unique ID field - autonumber type will serve. Try:

Query1: MinDateProcal
SELECT Table1.[Patient ID], Table1.[Test Name], Min(Table1.[Test Date]) AS [MinOfTest Date]
FROM Table1
WHERE (((Table1.[Test Name])="Procalcitonin"))
GROUP BY Table1.[Patient ID], Table1.[Test Name];

Query2: TestsProcalMinDate
SELECT Table1.ID, Table1.[Patient ID], Table1.[Test Name], Table1.[Test Value], Table1.[Test Date], MinDateProcal.[MinOfTest Date]
FROM MinDateProcal RIGHT JOIN Table1 ON MinDateProcal.[Patient ID] = Table1.[Patient ID]
WHERE (((Table1.[Test Date])<=[MinOfTest Date]));

Query3:
SELECT * FROM TestsProcalMinDate WHERE ID IN (SELECT TOP 1 Dupe.ID FROM TestsProcalMinDate As Dupe WHERE Dupe.[Patient ID]=TestsProcalMinDate.[Patient ID] AND Dupe.[Test Name]=TestsProcalMinDate.[Test Name] AND Dupe.[Test Date]<=TestsProcalMinDate.[MinOfTest Date] Order BY Dupe.[Test Date] DESC);


I don't see any reason to actually create second table. Just use the final query for whatever you intended to do with the table.

[June7]

The query works with the sample data you posted. I just deleted all records dated later than the first procalcitronin test and it still works.

If you want to provide db for analysis, follow instructions at bottom of my post.

[majahops]

I really appreciate your help. Your way did work on smaller data sets, but mine was just too large and it hung up on the third query. Clearly yours did work though. I figured out how to do it, but it was a much rougher way than your design. Thank you so much for your help, again. It really does mean the world!!!

The query works with the sample data you posted. I just deleted all records dated later than the first procalcitronin test and it still works.

If you want to provide db for analysis, follow instructions at bottom of my post.