Trouble trying to filter records in a subReportQuery 1

[houstonbill]

I accidentally put this on the wrong forum (other) and wanted to place this where it belongs.

I am wondering if/how I might filter the records that I need. The SQL below shows a sub-report that aligns the members “ContractNum” and “relcode” with the primary list of members in a report. The field “Compliant” can be Yes, No,NA or Unknown and there are 5 possible categories reported. I want this query print all members that may have a NO or UNKNOWN in anyone of the 5 possible categories; print the 5 YES and NO categories on their report. A person with a YES or NA in all areas should not print. I played around with some IIF statements but nothing seems to work. Upon looking at my SQL, do you have some thoughts?

SELECT Max(tblComplianceLog.RecvdDt) AS MaxOfRecvdDt, Last(tblComplianceLog.Compliant) AS LastOfCompliant, Last(tblComplianceLog.ComplianceDt) AS LastOfComplianceDt, tblComplianceLog.ContractNum, tblComplianceLog.CatID, tblComplianceLog.RelCode, tblCriteria.CriteriaDef, tbl_Category.CatName, tblCriteria.TimeFrame, tbl_Category.CatCode
FROM tblCriteria INNER JOIN (tbl_Category INNER JOIN tblComplianceLog ON tbl_Category.CatCode = tblComplianceLog.CatID) ON tblCriteria.CriteriaCode = tblComplianceLog.CriteriaID
GROUP BY tblComplianceLog.ContractNum, tblComplianceLog.CatID, tblComplianceLog.RelCode, tblCriteria.CriteriaDef, tbl_Category.CatName, tblCriteria.TimeFrame, tbl_Category.CatCode;

 

[SeeThru]

Id make a single calcualted field tat returns a boolean iof there is a compaint you want to add to your report.

It is then a simple matter to filter for where those are 'true'

 

[houstonbill]

Got it! Thanks for the advice. I knew there had to be a way but just couldn't get there.