Making a query that isolates records with the latest date

[John S. Ford, MD]

I have a table that has the following fields:

PatientPhysicianIDNum (autonumber and primary key)
PatientIDNum (number)
PhysicianIDNum (number)
PhysicianStartDate (date)

Every patient can have a series of physicians but only one at a time (each
with a different PhysicianStartDate referring to when the patient's
physician was replaced by the next one).

What I wish to do is create a query that yields one record for each patient
but with the CURRENT physician (i.e. the physician for that patient with the
LATEST PhysicianStartDate).

Any ideas on how I can set up such a query?

John

 

[John W. Vinson]

I have a table that has the following fields:

PatientPhysicianIDNum (autonumber and primary key)
PatientIDNum (number)
PhysicianIDNum (number)
PhysicianStartDate (date)

Every patient can have a series of physicians but only one at a time (each
with a different PhysicianStartDate referring to when the patient's
physician was replaced by the next one).

What I wish to do is create a query that yields one record for each patient
but with the CURRENT physician (i.e. the physician for that patient with the
LATEST PhysicianStartDate).

Any ideas on how I can set up such a query?

John

Using a Subquery.

Put a criterion on PhysicianStartDate of

=(SELECT Max(X.[PhysicianStartDate]) FROM yourtable AS X WHERE X.PatientIDNum
= yourtable.PatientIDNum)

This will not be updateable - to get an updateable recordset use the (slower)

=DMax("[PhysicianStartDate", "yourtable", "[PatientIDNum] = " &
[PatientIDNum])

 

[John S. Ford, MD]

Dear John,

Thank you very much for that! I used your second solution (only because I
understand how it works) and it worked perfectly.

John

I have a table that has the following fields:

PatientPhysicianIDNum (autonumber and primary key)
PatientIDNum (number)
PhysicianIDNum (number)
PhysicianStartDate (date)

Every patient can have a series of physicians but only one at a time (each
with a different PhysicianStartDate referring to when the patient's
physician was replaced by the next one).

What I wish to do is create a query that yields one record for each
patient
but with the CURRENT physician (i.e. the physician for that patient with
the
LATEST PhysicianStartDate).

Any ideas on how I can set up such a query?

John

Using a Subquery.

Put a criterion on PhysicianStartDate of

=(SELECT Max(X.[PhysicianStartDate]) FROM yourtable AS X WHERE
X.PatientIDNum
= yourtable.PatientIDNum)

This will not be updateable - to get an updateable recordset use the
(slower)

=DMax("[PhysicianStartDate", "yourtable", "[PatientIDNum] = " &
[PatientIDNum])