Help with complex Query

[CEV]

So I created this query in design view, as with any other query I have created. I have a form with a list of trainings and a check box next to each that will be checked once the person has completed that training. I ultimately want to create a report that only lists people that are currently here along with only listing what trainings they have not completed. I have the SQL view shown below to show what I have so far for the query. It is of course not showing what I want which is why I am here.



For one, the (((tblEmployees.DateLeftAgency) Is Null) part is not working. It is showing people that have left. And I thought ((tblEmployeePositions.DateStarted)=(SELECT Max(Temp.DateStarted) FROM tblEmployeePositions as Temp WHERE Temp.EmployeeNumber = tblEmployeePositions.EmployeeNumber)) would only show the last position they were in but some people are in the results more than once as they have held more than one position.

Finally, for each person it is showing all the training whether they have completed it or not. That is understandable, so will this need to be handled in the Report?

Thanks,
Chad

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblPositions.Department, tblEmployees.DateLeftAgency, 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
FROM ((tblDepartments INNER JOIN tblPositions ON (tblDepartments.DeptName = tblPositions.Department) AND (tblDepartments.DeptName = tblPositions.Department)) INNER JOIN (tblEmployees INNER JOIN tblEmployeePositions ON tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber) ON tblPositions.PositionNumber = tblEmployeePositions.PositionNumber) INNER JOIN tblTraining ON (tblEmployees.EmployeeNumber = tblTraining.EmployeeNumber) AND (tblEmployeePositions.EmployeeNumber = tblTraining.EmployeeNumber)
WHERE (((tblEmployees.DateLeftAgency) Is Null) AND ((tblEmployeePositions.DateStarted)=(SELECT Max(Temp.DateStarted) FROM tblEmployeePositions as Temp WHERE Temp.EmployeeNumber = tblEmployeePositions.EmployeeNumber)) AND ((tblTraining.OrienCompleted)=No) or ((tblTraining.CPRCompleted)=No) or ((tblTraining.PosSupCompleted)=No) or ((tblTraining.ManRepCompleted)=No) or ((tblTraining.[1TBCompleted])=No) or ((tblTraining.[2TBCompleted])=No) or ((tblTraining.DeptCheckCompleted)=No) or ((tblTraining.JobDesCompleted)=No) or ((tblTraining.EmergProCompleted)=No) or ((tblTraining.PersCareCompleted)=No) or ((tblTraining.[P&PCompleted])=No) or ((tblTraining.[1stAidCompleted])=No) or ((tblTraining.DietProcCompleted)=No) or ((tblTraining.ShadowCompleted)=No) or ((tblTraining.PersSpecCompleted)=No) or ((tblTraining.SupObsCompleted)=No) or ((tblTraining.MedAdminCompleted)=No) or ((tblTraining.CDS30Completed)=No) or ((tblTraining.CDS90Completed)=No) or ((tblTraining.SafeTransCompleted)=No)) OR (((tblTraining.RightsCompleted)=No)) OR (((tblTraining.FireCompleted)=No)) OR (((tblTraining.UnivPrecCompleted)=No)) OR (((tblTraining.SwallSafCompleted)=No))
ORDER BY tblPositions.Department;

[RuralGuy]

Did you mark this Solved in error? If not, would you like to share your solution with others that read this forum?