Querying for most recent value

[dhinton]

I am rather much of a novice when it comes to developing Access databases,
but have found myself in a situation at work where I have had to develop two
recently. I have a situation where I am trying to query the database to
determine placement types of all children in the database. A child may have
had more than one placement so I want to ensure that I am retrieving the most
recent placement type. This is my table structure:

Child Table: includes caseid (primary key), last name, first name, etc.
Placement Type: includes placement id (primary key), case id, date of
placement and placement type

If say caseid 3 has had five placement types as follows:
1/1/2009 - Foster Home
3/6/2009 - Group Home
4/15/2009 - Hospital
4/20/2009 - Residential Treatment Center
9/25/2009 - Group Home

How do I create a query that will return the result:
Caseid 3 Placement Type - Group Home Date of Placement 9/25/2009

 

[PieterLinden via AccessMonster.com]

I am rather much of a novice when it comes to developing Access databases,
but have found myself in a situation at work where I have had to develop two
recently. I have a situation where I am trying to query the database to
determine placement types of all children in the database. A child may have
had more than one placement so I want to ensure that I am retrieving the most
recent placement type. This is my table structure:

Child Table: includes caseid (primary key), last name, first name, etc.
Placement Type: includes placement id (primary key), case id, date of
placement and placement type

If say caseid 3 has had five placement types as follows:
1/1/2009 - Foster Home
3/6/2009 - Group Home
4/15/2009 - Hospital
4/20/2009 - Residential Treatment Center
9/25/2009 - Group Home

How do I create a query that will return the result:
Caseid 3 Placement Type - Group Home Date of Placement 9/25/2009

It's easiest if you do it in 2 parts (queries). The first query gets the
last placement date for each patient

(this is DHintonQ1)
SELECT DHinton.PatientID, Max(DHinton.AdmitDate) AS MaxOfAdmitDate
FROM DHinton
GROUP BY DHinton.PatientID;

Then you join that result back to the original table to get the
TreatmentType/Admit type...

SELECT DHintonQ1.PatientID, DHintonQ1.MaxOfAdmitDate, DHinton.TreatmentType
FROM DHintonQ1 INNER JOIN DHinton ON (DHintonQ1.MaxOfAdmitDate = DHinton.
AdmitDate) AND (DHintonQ1.PatientID = DHinton.PatientID);