I have recently been given the task of getting data based on a number of criteria for a research grant proposal. The data is all audiological information containing thresholds at various frequencies for different types of tests, so there are about 10 different tables I have to query from with varying numbers of fields in each.
For this particular query, the person who asked for my help would like to include subjects' data from five different tables, but wants as many rows as possible, and as such asked for subjects to be included even if they were not tested on all five different tests (to make the research grant proposal more impressive or something along those lines).
I have been trying to figure out how to include blank rows for subjects where test data is not available, but am not getting the number of rows that I expected. The following is the query I have now, though I have left out all of the data fields to try to make things a bit easier to follow (my apologies for the poor formatting, this is as-generated by the query builder in 2003):
CODE
SELECT Subject.[Subject#], PureTone.[Test Date], HighFrequency.[Test Date], SpeechTest.[Test Date], DPOAE.[Test Date], TEOAE.[Test Date]
FROM ((((Subject
LEFT JOIN TEOAE ON Subject.[Subject#] = TEOAE.[Subject#])
LEFT JOIN PureTone ON Subject.[Subject#] = PureTone.[Subject#])
LEFT JOIN DPOAE ON Subject.[Subject#] = DPOAE.[Subject#])
LEFT JOIN SpeechTest ON Subject.[Subject#] = SpeechTest.[Subject#])
LEFT JOIN HighFrequency ON Subject.[Subject#] = HighFrequency.[Subject#]
WHERE (((Subject.[Genetics#]) Like "###-[AB]" Or (Subject.[Genetics#]) Like "###-?-B" Or (Subject.[Genetics#]) Like "###-?B")
AND ((PureTone.[Test Date])=
(SELECT Max([Test Date]) FROM PureTone
WHERE [Subject#] = Subject.[Subject#]))
AND ((PureTone.Ear)="Right")
AND (((DateDiff("d",[Subject]![Date of Birth],[PureTone]![Test Date]))/365)>58)
AND ((HighFrequency.[Test Date])=
(SELECT Max([Test Date]) FROM HighFrequency
WHERE [Subject#] = Subject.[Subject#]))
AND ((HighFrequency.Ear)="Right")
AND ((SpeechTest.[Test Date])=
(SELECT Max([Test Date]) FROM SpeechTest
WHERE [Subject#] = Subject.[Subject#]))
AND ((SpeechTest.Ear)="Right")
AND ((DPOAE.[Test Date])=
(SELECT Max([Test Date]) FROM DPOAE
WHERE [Subject#] = Subject.[Subject#]))
AND ((DPOAE.Ear)="Right")
AND ((DPOAE.Repetition)=1)
AND ((DPOAE.Type)="Response")
AND ((TEOAE.[Test Date])=
(SELECT Max([Test Date]) FROM TEOAE
WHERE [Subject#] = Subject.[Subject#]))
AND ((TEOAE.Ear)="Right"))
ORDER BY Subject.[Subject#];
FROM ((((Subject
LEFT JOIN TEOAE ON Subject.[Subject#] = TEOAE.[Subject#])
LEFT JOIN PureTone ON Subject.[Subject#] = PureTone.[Subject#])
LEFT JOIN DPOAE ON Subject.[Subject#] = DPOAE.[Subject#])
LEFT JOIN SpeechTest ON Subject.[Subject#] = SpeechTest.[Subject#])
LEFT JOIN HighFrequency ON Subject.[Subject#] = HighFrequency.[Subject#]
WHERE (((Subject.[Genetics#]) Like "###-[AB]" Or (Subject.[Genetics#]) Like "###-?-B" Or (Subject.[Genetics#]) Like "###-?B")
AND ((PureTone.[Test Date])=
(SELECT Max([Test Date]) FROM PureTone
WHERE [Subject#] = Subject.[Subject#]))
AND ((PureTone.Ear)="Right")
AND (((DateDiff("d",[Subject]![Date of Birth],[PureTone]![Test Date]))/365)>58)
AND ((HighFrequency.[Test Date])=
(SELECT Max([Test Date]) FROM HighFrequency
WHERE [Subject#] = Subject.[Subject#]))
AND ((HighFrequency.Ear)="Right")
AND ((SpeechTest.[Test Date])=
(SELECT Max([Test Date]) FROM SpeechTest
WHERE [Subject#] = Subject.[Subject#]))
AND ((SpeechTest.Ear)="Right")
AND ((DPOAE.[Test Date])=
(SELECT Max([Test Date]) FROM DPOAE
WHERE [Subject#] = Subject.[Subject#]))
AND ((DPOAE.Ear)="Right")
AND ((DPOAE.Repetition)=1)
AND ((DPOAE.Type)="Response")
AND ((TEOAE.[Test Date])=
(SELECT Max([Test Date]) FROM TEOAE
WHERE [Subject#] = Subject.[Subject#]))
AND ((TEOAE.Ear)="Right"))
ORDER BY Subject.[Subject#];
The minimum requirements I have are that there be only one row of data for each subject (which is accomplished through the "Select Max..." subqueries), that the "Genetics#" match the format specified, and that the subject be at least 58 years old. I'm certain that having the extremely long WHERE clause is causing the problem of not getting null rows, but I don't know how to overcome this problem. I was hoping it would be a matter of using LEFT JOINs in place of INNER JOINs which I had before, but this doesn't seem to be working at all.
If you can decipher what I've posted as code and think you can help, I'd be very appreciative!
Thanks,
Jamie