Returning ONLY the latest records in a query

kilkerr1 · November 3rd, 2003, 07:10 AM

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.

pgtips · November 3rd, 2003, 08:59 AM

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

SerranoG · November 3rd, 2003, 09:21 AM

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

tjw · November 3rd, 2003, 01:03 PM

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.