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

[billywhizz]

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!

 

[pbaldy]

Would adapting this fit the situation?

http://www.baldyweb.com/LastValue.htm

 

[plog]

I think you have a few structural issues.

1. First and leastmost, you have seperate fields in both tAdmission and tNotes for DAte and Time. These should both have only one field which contains both Date and Time (i.e. a Date/Time field).

Now the big 2, which are related:

2. tNotes shouldn't contain a HospitalNumber field. Since notes are related to Admissions it should contain a link to Admissions.

3. Admissions needs its own unique ID so that it can be directly linked to notes. This should be a numeric autonumber.

And also 2 notes: A. since you are using INNER JOINS you will only see patients who have admissions and notes. If they don't have a note, they won't appear at all in your query. This may or may not be what you want. B. Choose names and stick with them--Clients/Patients and Admissions/Episodes make it confusing both internally (I work at a Hospital and know how much healthcare loves synonyms) and externally (I had to read through your posting a few times to see that Episodes in your explanations translated to Admissions in your SQL).

 

[nanscombe]

You may want a simple autonumber for the patient, to be used as a link to Admissions, I would see Hospital number being a Social Security or National Insurance type Alphanumeric string. That may be best suited as the link to other tables.

2. tNotes shouldn't contain a HospitalNumber field. Since notes are related to Admissions it should contain a link to Admissions.

3. Admissions needs its own unique ID so that it can be directly linked to notes. This should be a numeric autonumber.
.

Wouldn't notes be linked to a client (patient) rather than admissions?

A client may be admitted multiple times but their previous notes may still be relevant?

Hmm, don't know. Belay that. You'd probably still have to link notes through a particular admission wouldn't you?

Client -> Admission -> Notes

 

[billywhizz]

Thanks all for your input which has led to the query below which delivers.

Design considerations duly noted. I'm not sure there were any at the start I just began on the basis of I wonder if I can do this quicker and cheaper than the quotes. My latter history was with Lotus Notes/Domino about 9 years of that, but hey it's all script.

Before reading this (if you can be bothered) let me remind you that my major SQL experience was gained a while ago... on IBM's brand new SQL/DS on the VM/CMS operating system on an IBM 3090. Prizes for the year, answers on a postcard.

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

subquery.....

SELECT
tNotes.HospitalNumber, tNotes.EpisodeDate, tNotes.EpisodeTime, Max(tNotes.CreationDateTime) AS MaxDate
FROM
tNotes
WHERE
tNotes.Source="ADMISSION"
GROUP BY
tNotes.HospitalNumber, tNotes.EpisodeDate, tNotes.EpisodeTime;