Add yes/no field based on results of query (1 Viewer)

[Kayleigh]

Trying to write query to put yes/no in field based on whether a record appears in database and meets specific condition. But could not get this together in one query. Tried multiple combinations of IIF function, dlookup and isNull but could not get to work when in one field - it displays as error when query is run.

This should be the first part of calculation (if safeguarding = true, display yes):

Completed: IIf(DLookUp("fldTrainingCat","lkptblTrainingCat","fldTrainingCatID=" & [fldTSTrainingCat])="Safeguarding","Yes",(IIf([NotCompleted]=True,"No","")))

This should be the second part of the calculation (if no associated records, display no):

NotCompleted: IIf(IsNull([fldSessionID])=True,"No","")

Appreciate your help once again

 

[plog]

First, Dlookup has no business in a query. You've already got lkptblTrainingCat in the query, no need to Dlookup to it, just reference the data you need, or possibly build a sub query to get that data.

Second, you never explained the criteria. Don't recite code back to me, tell me in english under what conditions should Completed show "Yes", under what conditions it should show "No".

Third, your logic isn't air tight. One would think that if something wasn't completed it would be incomplete, but that's not the case. You're [NotCompleted] field logic isn't mutually exclusive of your Completed field logic. Lastly, and in the same vein, your Completed field allows 3 outcomes--"Yes", "No" and "". Is that what you want?

Again, in english the criteria.

 

[Kayleigh]

Query in black n white:
Field completed to display 'yes' for those staff who have completed safeguarding training within last year and 'no' for those who have not (either because expired or not on record).

I did a dlookup to find the training records which were categorised as safeguarding but if lookup can be done within query then all the better!
Since I am checking against staff list where some are not at all in the staff-training table there will be null records in results but these should be classified as a no.

Hope this is more reasonable.

 

[plog]

This requires a subquery. In it you find out who meets the criteria, then in the main query you use it to designate all staff as either complete/incomplete. Here's the SQL for the subquery:

SELECT jtblStaffTraining.fldStaffID AS CompletedTrainingID
FROM jtblStaffTraining INNER JOIN (lkptblTrainingCat INNER JOIN tblTraining ON lkptblTrainingCat.fldTrainingCatID = tblTraining.fldTSTrainingCat) ON jtblStaffTraining.fldSessionID = tblTraining.fldTSessionID
WHERE (((lkptblTrainingCat.fldTrainingCat)="Safeguarding") AND ((tblTraining.fldTSDate)>=Date()-365));

Paste it into a new query object and name it 'sub1'. Then, use it in a new query to classify everyone in tblStaff with this query:

SELECT tblStaff.fldStaffID, IIf(IsNull([CompletedTrainingID]),"No","Yes") AS Completed
FROM tblStaff LEFT JOIN sub1 ON tblStaff.fldStaffID = sub1.CompletedTrainingID;

I did not use [fldTDateExpired] and instead made it calculate from the current day back a year. If you wanted to edit it so that it does use that field you would do so in sub1. Further, to make this work for all training and find out the status of everyone for every training you would modify sub1.