Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 3rd, 2003, 07:10 AM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Returning ONLY the latest records in a query

Hi all

Something I've been struggling with for a while:

I have 2 tables, tblClient and tblEpisode. Each client (details such as client_forename and client_surname are recorded in tblClient) may have one or more than one contact episode (details such as episode_id, episode_start_date and episode_dose are recorded in tblEpisode). The relationship is a one-to-many and the join is made by the client_id field.

I want to create a query which shows details of ONLY the latest episode for every client. Fields I am trying to return are:

- tblClient.client_forename
- tblClient.client_surname
- tblEpisode.episode_dose
- tblEpisode.episode_total_dose (this is a calulated field: the user inputs how many days they want and the episode_dose for each client is multiplied by this number of days)

At the moment I am getting back details from all episodes that a client may have had. I've tried using Group By MAX function in the episode_start_date field, and the Group By MAX function in the episode_id field (an autonumber primary key for each episode which increases for each episode added) but these still show ALL the episodes for each client rather than just the latest addedd. I guess it must be a bit more complicated than that...

Please help - I'm getting desperate!

Thanks.



 
Old November 3rd, 2003, 08:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

I can give you the SQL to extract the latest record, but it involves subqueries and Access has its own peculiar syntax for them so you will need an Access expert (i.e not me ;)) to translate it into Jet SQL.

Method 1 - correlated subquery (join the table to itself)
Code:
SELECT client_id, episode_dose
FROM tblEpisode AS e1
WHERE episode_start_date = 
  (
   -- this subquery gets the latest date for each client
   SELECT MAX(episode_start_date)
   FROM tblEpisode AS e2
   Where e1.client_id = e2.client_id
   )
Method 2 - normal subquery (join the table to a temp table)
Code:
SELECT e1.client_id, e1.episode_dose
FROM tblEpisode AS e1
INNER JOIN
  (
   SELECT client_id, MAX(episode_start_date)
   FROM tblEpisode
   GROUP BY client_id
   ) AS e2
ON e1.client_id = e2.client_id
  AND e1.episode_start_date = e2.episode_start_date
Both of the above methods return the client_id and dose associated with the latest record in tblEpisode for each client. You then need to join tblClient to get the results you need - but I didn't include that because I didn't want to obscure the details of how to get the latest record from tblEpisode.

hth
Phil
 
Old November 3rd, 2003, 09:21 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

In your query's Query Design Toolbar next to the Sum button (sigma) there is a dropdown that usually says "ALL". Changing that will restrict the query to give you, for example, the top 5 answers, the top 10% of the list, etc. Just change that number.

A number alone gives you the max number of results returned, e.g. 5, 10, 12, etc. Putting a percent gives you the top X% returned. If you were to sort by descending date and then restrict the number in the dropdown, e.g. enter the number 3, you'd get the three most recent dates.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old November 3rd, 2003, 01:03 PM
tjw tjw is offline
Registered User
 
Join Date: Sep 2003
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In your query along with Max in the Group By insert Last under the Episode ID - this should give the latest records from your tables.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Query for getting latest date and following .. dragonball SQL Server 2000 4 February 28th, 2008 08:10 AM
Query returning all records - why? Toiletbrush Infopath 1 November 21st, 2007 03:19 AM
Query returning No Records englandera Classic ASP Databases 2 November 22nd, 2004 10:54 AM
Selecting the Latest Records gordbro Access 3 September 16th, 2004 07:21 PM
two tables, one query, getting the latest for each eln MySQL 2 November 17th, 2003 11:15 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.