show records who haven't been trained last year. 1

[hsingh1981]

Hi all,

I'm doing query and i can show records who have been trained.....how do i show staff records who haven't been trained. Not done query like this before?

This shows all records who have been trained with paticular date and course criteria.

SELECT R_Area.Area, R_TrainingCourses.Course, [Max TrainingDate].CourseID, [Max TrainingDate].[Max Training Date], [Surname] & ", " & [Firstname] AS [Staff Name], Staff.Status, Staff.Grade, Staff.Area, Staff.StaffType
FROM ((Staff INNER JOIN R_Area ON Staff.Area = R_Area.ID) LEFT JOIN [Max TrainingDate] ON Staff.StaffID = [Max TrainingDate].StaffID) LEFT JOIN R_TrainingCourses ON [Max TrainingDate].CourseID = R_TrainingCourses.ID
WHERE ((([Max TrainingDate].CourseID)=[forms]![Mand Training].[cboCourse].[value]) AND (([Max TrainingDate].[Max Training Date])<=[forms]![Mand Training].[txtLastTrained].[value]) AND ((Staff.Status)="EMPLOYED") AND ((Staff.Grade)<>"<>") AND ((Staff.Area)=[forms]![Mand Training].[cboArea].[value]) AND ((Staff.StaffType)="N")) OR ((([Max TrainingDate].[Max Training Date]) Is Null) AND ((Staff.Status)="EMPLOYED") AND ((Staff.Grade)<>"<>") AND ((Staff.Area)=[forms]![Mand Training].[cboArea].[value]) AND ((Staff.StaffType)="N"))
ORDER BY R_Area.Area, R_TrainingCourses.Course, [Surname] & ", " & [Firstname];

my question is how do is show only records who haven't been trained?

many thanks

 

[dhookom]

I don't want to begin evaluating your SQL. Assuming you wanted to query all of the Customers in Northwind who hadn't made an order between 1/1/1996 and 12/31/1996, the SQL would be:

SELECT Customers.*
FROM Customers
WHERE CustomerID Not In (SELECT CustomerID FROM Orders WHERE OrderDate Between #1/1/1996# and #12/31/1996#);

Duane
Hook'D on Access
MS Access MVP