I create my Queries via the design view as the SQL view is a little complicated at this point. I know how to get the query to only show records based on the criteria for one column. In the query below, all the fields in tblTraining that has the word completed in it is a yes/no field. Once all those fields are checked yes, I no longer want the record for that employee shown. How can I do this? Thanks. We are a small non-profit and I am one person that does a little of everything and am an expert in nothing.
SELECT tblEmployees.LastName, tblEmployees.FirstName, tblPositions.Department, tblEmployeePositions.PositionNumber, tblEmployees.DateofHire, tblEmployees.DateLeftAgency, tblEmployeePositions.DateStarted, tblTraining.OrienCompleted, tblTraining.RightsCompleted, tblTraining.FireCompleted, tblTraining.UnivPrecCompleted, tblTraining.SwallSafCompleted, tblTraining.CPRCompleted, tblTraining.PosSupCompleted, tblTraining.ManRepCompleted, tblTraining.[1TBCompleted], tblTraining.[2TBCompleted], tblTraining.DeptCheckCompleted, tblTraining.JobDesCompleted, tblTraining.EmergProCompleted, tblTraining.PersCareCompleted, tblTraining.[P&PCompleted], tblTraining.[1stAidCompleted], tblTraining.DietProcCompleted, tblTraining.ShadowCompleted, tblTraining.PersSpecCompleted, tblTraining.SupObsCompleted, tblTraining.MedAdminCompleted, tblTraining.CDS30Completed, tblTraining.CDS90Completed, tblTraining.SafeTransCompleted, tblTraining.MedAdminRenewalDate, tblTraining.PTInterviewCompleted
FROM (tblDepartments INNER JOIN tblPositions ON (tblDepartments.DeptName = tblPositions.Department) AND (tblDepartments.DeptName = tblPositions.Department)) INNER JOIN (tblEmployees INNER JOIN (tblEmployeePositions INNER JOIN tblTraining ON tblEmployeePositions.EmployeeNumber = tblTraining.EmployeeNumber) ON (tblEmployees.EmployeeNumber = tblTraining.EmployeeNumber) AND (tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber)) ON tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
WHERE (((tblEmployees.DateLeftAgency) Is Null) AND ((tblEmployeePositions.DateStarted)=(SELECT Max(Temp.DateStarted) FROM tblEmployeePositions as Temp WHERE Temp.EmployeeNumber = tblEmployeePositions.EmployeeNumber)))
ORDER BY tblPositions.Department;