Left Join With Subquery

[GregD]

Table tblReportPatients has 23 PatientIds in it. tblDemographic has a 1 to many relationship and is filled with demographics values for each patient ID. A patient could have multiple "Grade Completed" rows in tblDemographic and each one has an EncounterDate when it was recorded. The query below returns the last "Grade Completed" value for each patient that has one or more "Grade Completed" rows in tblDemographic. Only 20 of those patients has a "Grade Completed", so I get 20 rows. What I would like to do is return 23 rows - one for each row in tblReportPatients - and have the 3 that have no row in tblDemographic show up as either a Null value or "Unreported". The second version of the query tries to use the Iif() function to return "Unreported", but it doesn't work. Any help is appreciated.

Greg

SELECT tblReportPatients.PatientID, 
                    [Value] AS Grade
FROM tblReportPatients LEFT JOIN tblDemographic ON tblReportPatients.PatientID = tblDemographic.PatientID
WHERE 
(((tblDemographic.ItemName)="Grade Completed") 
AND ((tblDemographic.EncounterDate)=(SELECT Max(Q.EncounterDate) 
FROM tblDemographic As Q WHERE Q.ItemName='Grade Completed' AND Q.PatientID = tblDemographic.PatientID)));

SELECT tblReportPatients.PatientID, 
    IIf(IsNull([Value]),"unreported",[Value]) AS Grade
FROM tblReportPatients LEFT JOIN tblDemographic ON tblReportPatients.PatientID = tblDemographic.PatientID
WHERE 
(((tblDemographic.ItemName)="Grade Completed") 
AND ((tblDemographic.EncounterDate)=(SELECT Max(Q.EncounterDate) 
FROM tblDemographic As Q WHERE Q.ItemName='Grade Completed' AND Q.PatientID = tblDemographic.PatientID)));

 

[SOS]

You can't use criteria on a table that is the one in the outer join. You would need to use another query first to select the criteria and then do an outer join on it with the other table.

 

[SOS]

And this might help explain it better.

 

[GregD]

You can't use criteria on a table that is the one in the outer join. You would need to use another query first to select the criteria and then do an outer join on it with the other table.

So then there is no way to do this with one query?

The query below achieves what I want, but does it with 2 queries. This assumes that I call the first query above qryReportEdu and call it in the query below.

SELECT tblReportPatients.PatientID, IIf(IsNull([Education]),"Unreported",[Education]) AS Edu
FROM tblReportPatients LEFT JOIN qryReportEdu ON tblReportPatients.PatientID = qryReportEdu.PatientID;