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
Similar Threads
Returning ONLY the latest records in a query
