Returning ONLY the latest records in a query

[kilkerr1]

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.

 

[Tom Ellison]

Dear Kilk:

This is done by filtering the results to the "most recent contact
episode" of each client. This might look like:

SELECT C.client_forename, C.client_surname, E.episode_dose,
E.episode_total_dose
FROM tblClient C
INNER JOIN tblEpisode E ON E.client_ID = C.client_ID
WHERE E.episode_date = (SELECT MAX(episode_date) FROM tblEpisode E1
WHERE E1.client_ID = E.client_ID)

I have had to make some guesses on how to write this, such as
episode_date and client_ID fields. If you have trouble applying this,
that's probably the reason.

If you need better help, please post a query showing how the client
and episode tables should be joined, and showing the episode date
column which is to be used to find the most recent date.

Note that, if there are two episode rows with the same "most recent
date" then the query does not have sufficient information to choose
between them, and both will be shown. This may depend on whether the
"most recent date" is date only, or if it contains a time of day value
as well. By being more granular in this way it becomes much less
likely to have two rows with the same date/time, but even that's no
guarantee. If you truly require only one row be returned, some
additional "tie breaker" may be necessary.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Jeff Boyce]

?Your tblEpisode has ClientID, EpisodeStartDate, and other fields?

What happens if you create a Totals query on tblEpisode, GroupBy ClientID,
and Max EpisodeStartDate? That should give you each ClientID and the max
(i.e., most recent) EpisodeStartDate.

With that query working, you could create another query, joining the first
back to the tblEpisode to retrieve the other fields where ClientID and
EpisodeStartDate match. And then join that (second) query to the tblClient
for client details.

Good luck!

Jeff Boyce
<Access MVP>