Change in query to Populate Yes,No and NA options in the listbox

**AmanKaur123** · 05-05-2017, 02:24 AM

Hi All

This is my query which works fine so when the AgentSignOff=true
then it displays 'Yes' otherwise 'No' . So this way for all the records
AgentSignOff gets populated with "Yes" or "No".
but I just want to amend it
so that it should check if the record is present in tbl_RMS_Paperless then

checks the following : AgentSignOff=true then it displays 'Yes' otherwise
'No'.

If the record is not present in tbl_RMS_Paperless but it is present
in tblAudit then populate AgentSignOff=NA for those
records.

Code:

SELECT tblAudit.AuditID, tblAudit.[Staff Number], 
tblAudit.[Date of Observation], tblCallType.[Call Type], 
tblAuditPurpose.Purpose, tblObjective.Objective, tblOutcome.Outcome, 
IIf([AgentSignOff]=True,"Yes","No") AS Expr1, IIf([SupSignOff]=True,"Yes","No") 
AS Expr2
FROM tbl_RMS_Paperless RIGHT JOIN (tblOutcome INNER JOIN 
(tblObjective INNER JOIN (tblAuditPurpose INNER JOIN (tblCallType INNER JOIN 
tblAudit ON tblCallType.[Call TypeID] = tblAudit.[Call TypeID]) ON 
tblAuditPurpose.[Audit PurposeID] = tblAudit.[Audit PurposeID]) ON 
tblObjective.ObjectiveID = tblAudit.ObjectiveID) ON tblOutcome.OutcomeID = 
tblAudit.OutcomeID) ON tbl_RMS_Paperless.ActivityRef = tblAudit.AuditID
WHERE 
(((tblAudit.[Staff Number])=[cbomoveto]))
ORDER BY tblAudit.[Date of 
Observation] DESC , tblAudit.AuditID DESC;

The listbox rowsource is set with the query finally as below:

Code: