query for max date grouped by multiple IDs

[WalkieTalkie]

I have a table showing date, lab test type, and Lab test results for each patient eg:

PatientTestID   PatientID   TestID   TestDate   TestResult
1               10          x        15/1/06     105
2               10          y        15/1/06     200
3               15          x        10/10/06   678
4               10          x        13/1/06     209
5               15          y        10/3/06    765
6               15          y        12/3/06    890
7               10          y        17/1/06     23

I want a query to show me the latest test result for each type of test, for each patient. eg the results I want are:

PatientTestID   PatientID   TestID   TestDate   TestResult
1               10          x        15/1/06     105
3               15          x        10/10/06   678
6               15          y        12/3/06    890
7               10          y        17/1/06     23

I am able to make a totals query that shows the max(TestDate) for each PatientID. Likewise I can do another one for max(TestDate) for each TestID, but I can't for the life of me work out how to combine the two so that I get max(TestDate) for each TestID, for each patient. I also need to include the TestResult field. No matter what I do I can't get the right results. I would love someone to show me how!
Thanks in advance

 

[PHV]

SELECT A.PatientTestID, A.PatientID, A.TestID, A.TestDate, A.TestResult
FROM yourTable AS A INNER JOIN (
SELECT PatientID, TestID, Max(TestDate) AS LastDate FROM yourTable GROUP BY PatientID, TestID
) AS L ON A.PatientID = L.PatientID AND A.TestID = L.TestID AND A.TestDate = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WalkieTalkie]

Thanks for that. I've tried it and get an error "Syntax error in FROM clause". I've copied it straight from your reply - what's missing?

 

[PHV]

Change yourTable with the real table name ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WalkieTalkie]

Yes. In fact I had simplified my field names in my original posting, so have replaced those too. Here's exactly what I have tried and got the error message with:

SELECT A.PersonTestID, A.HospitalNumber, A.TestID, A.TestDateDone, A.Hb
FROM tblPersonTest AS A INNER JOIN (SELECT HospitalNumber, TestID, Max(TestDateDone) AS LastDate FROM tblPersonTest GROUP BY HospitalNumber, TestID) AS L ON A.HospitalNumber = L.HospitalNumber AND A.TestID = L.TestID AND A.TestDateDone = L.TestDateDone

Thanks for your help...

 

[PHV]

Replace this:
AND A.TestDateDone = L.TestDateDone
with this:
AND A.TestDateDone = L.LastDate

BTW, this SQL is for ac2k or above.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WalkieTalkie]

Good spotting - I've changed t and now it looks like this:

SELECT A.PersonTestID, A.HospitalNumber, A.TestID, A.TestDateDone, A.Hb
FROM tblPersonTest AS A INNER JOIN (
SELECT HospitalNumber, TestID, Max(TestDateDone) AS LastDate FROM tblPersonTest GROUP BY HospitalNumber, TestID
) AS L ON A.HospitalNumber = L.HospitalNumber AND A.TestID = L.TestID AND A.TestDateDone = L.LastDate

Same error though.

This db is in Access 97 - is that the problem? If so, is there a way around it - I really don't want to get into converting it at this stage if at all possible...

 

[lespaul]

the work around is to save this:

SELECT HospitalNumber, TestID, Max(TestDateDone) AS LastDate FROM tblPersonTest GROUP BY HospitalNumber, TestID

as a query: qryMaxTests

and make this modification to your actual query:

SELECT A.PersonTestID, A.HospitalNumber, A.TestID, A.TestDateDone, A.Hb
FROM tblPersonTest AS A INNER JOIN [b]qryMaxTests[/b] AS L ON A.HospitalNumber = L.HospitalNumber AND A.TestID = L.TestID AND A.TestDateDone = L.LastDate

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[WalkieTalkie]

How can it be so simple?!
That's brilliant; it works a treat. Thanks both of you for spending your time on this. Despite the simplicity, I just would never have got it on my own!