Prev: AVG BETWEEN DAYS
Next: Optimization of Between Clause Queries
From: dhinton on 4 Jun 2010 16:07
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

From: PieterLinden via AccessMonster.com on 4 Jun 2010 20:43
dhinton wrote:
>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);

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201006/1

 | 
Pages: 1
Prev: AVG BETWEEN DAYS
Next: Optimization of Between Clause Queries