It's been 15 years since I wrote any SQL....

I have three tables client, episode, notes

there is a unique client id

there are multiple episodes per client identified uniquely by date/time

there are multiple notes per episode with a unique creation date/time

I want to query joining the three tables and end up with one record that displays the client, episode date/time and last note created for that episode.

So it'll look something like this.....

SELECT tClients.HospitalNumber, tClients.FirstName, tClients.Surname, tAdmissions.AdmissionDate, tAdmissions.AdmissionTime, tNotes.NoteShort
FROM (tAdmissions INNER JOIN tClients ON tAdmissions.HospitalNumber = tClients.HospitalNumber) INNER JOIN tNotes ON (tAdmissions.AdmissionTime = tNotes.EpisodeTime) AND (tAdmissions.AdmissionDate = tNotes.EpisodeDate) AND (tClients.HospitalNumber = tNotes.HospitalNumber)
WHERE tAdmissions.Discharge=False

and then what......

AND (tNotes.CreationDateTime= (SELECT max(tNotes.CreationDateTime) FROM tNotes));

....should this go somewhere.

Has to run in ACCESS 2003!