If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Querying for most recent value
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 structu 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 |
#2
|
|||
|
|||
Querying for most recent value
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 structu 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/For...eries/201006/1 |
Thread Tools | |
Display Modes | |
|
|