A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Querying for most recent value



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 09:07 PM posted to microsoft.public.access.queries
DHinton
external usenet poster
 
Posts: 6
Default 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  
Old June 5th, 2010, 01:43 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.