SQL to Select the last record for each Patient

[Wissam]

Hi,
The following is an example of a QUERY (qryApt) based on a table of patient
appointments (tbleApt) with primary key field called Apt#; the query is
ordered by PatientID and by AptDate (ascending):

Apt# PatientID AptDate BloodPressure
1 A1 1/1/2008 Normal
2 A1 2/1/2008 High
3 A1 3/1/2008 Normal
4 B1 1/7/2008 High
5 B1 2/3/2008 Normal
6 B2 4/2/2008 High

I need an SQL/code for a query (call it "qryLastApts") that does the
following: "for each patientID in qryApt, select only the last record for
that PatientID"?

Thanks a lot.

 

[banem2]

Hi,
The following is an example of a QUERY (qryApt) based on a table of patient
appointments (tbleApt) with primary key field called Apt#; the query is
ordered by  PatientID and by AptDate (ascending):

Apt#     PatientID      AptDate           BloodPressure
1           A1               1/1/2008         Normal
2           A1               2/1/2008         High
3           A1               3/1/2008         Normal
4           B1                1/7/2008         High
5           B1                2/3/2008        Normal
6           B2                4/2/2008        High

I need an SQL/code for a query (call it "qryLastApts") that does the
following: "for each patientID in qryApt, select only the last record for
that PatientID"?

Thanks a lot.

SELECT tbleApt.PatientID, Max(tbleApt.AptDate) AS MaxOfAptDate
FROM tbleApt
GROUP BY tbleApt.PatientID
ORDER BY tbleApt.PatientID, Max(tbleApt.AptDate) DESC

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[DomThePom]

I can do this using 2 queries - may be possible with 1 but it is Monday
morning....

First (sub) query - qryLastApptDate:

SELECT tbleApt.PatientID, Max(tbleApt.AptDate) AS LastAptDate
FROM tbleApt
GROUP BY tbleApt.PatientID;

This returns the last (max) date for each patient

2nd query: qryLastApptRecord

SELECT tbleApt.*
FROM qryLastApptDate INNER JOIN tbleApt ON (qryLastApptDate.LastAptDate =
tbleApt.AptDate) AND (qryLastApptDate.PatientID = tbleApt.PatientID);

This returns the related record to the last appointment date for each patient

 

[DomThePom]

This does not produce the last record for each patient, just a list of
patient IS's and the date of their last visit - can you get the last record
for each patient into one query?

 

[John Spencer]

First, do everyone (including yourself) and change the field title from
Apt# to AptNum. When you use characters other than letters, numbers,
and the underscore to name tables, queries, and fields that forces you
to surround the name with square brackets. That means you cannot use a
subquery in your FROM clause if the subquery requires that field and
that means you need to use two queries to get the result you seek.

Fortunately, you only "sinned" on the one field and it is not needed in
the subquery. So you should be able to use an SQL statement that look
like the following.

SELECT [Apt#], PatientID, AptDate, BloodPressure
FROM tbleApt INNER JOIN
(SELECT PatientID, Max(AptDate) as LastDate
FROM tbleApt
GROUP BY PatientID) as B
ON tbleApt.PatientID = B.PatientID
AND tbleApt.AptDate = B.LastDate

Open up your query in design view, switch to SQL view, and enter the
above into the SQL query window. Then run it.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Wissam]

Thanks a lot.
This worked.

I can do this using 2 queries - may be possible with 1 but it is Monday
morning....

First (sub) query - qryLastApptDate:

SELECT tbleApt.PatientID, Max(tbleApt.AptDate) AS LastAptDate
FROM tbleApt
GROUP BY tbleApt.PatientID;

This returns the last (max) date for each patient

2nd query: qryLastApptRecord

SELECT tbleApt.*
FROM qryLastApptDate INNER JOIN tbleApt ON (qryLastApptDate.LastAptDate =
tbleApt.AptDate) AND (qryLastApptDate.PatientID = tbleApt.PatientID);

This returns the related record to the last appointment date for each patient