December 3rd, 2012, 12:32 PM
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)
and then what......
AND (tNotes.CreationDateTime= (SELECT max(tNotes.CreationDateTime) FROM tNotes));
....should this go somewhere.
Has to run in ACCESS 2003!