Hi, I have a database which keeps records of employee's training they have done. It also keeps a record of which training is required.
In my employee table is a field which is a yes/no called IsLGVDriver. This is to determine whether the employee is a truck driver or not. (LGV = Large Goods Vehicle)
I have a table to store Training course categories, there is 16 in there at the moment. 1 of which is related to truck driving as it to do with safety and fuel efficient driving (ID = 6). A course category can have many employees, and an employee can have many course categories (ie required to be trained).
Because of the many-to-many I've got a bridge table called CourseRequirements just containing employee ID and category ID.
I have a query setup to find all employees that are marked as non-LGV drivers, but the employee requires the safety and fuel efficient driving. This is to identify which employees have wrongly been entered to need the training because they're not a truck driver:
What I need now is the opposite of this. To find all truck drivers that are missing the safety and fuel efficient driving requirement from the CourseRequirements table. But I don't quite know how to do this.
In my employee table is a field which is a yes/no called IsLGVDriver. This is to determine whether the employee is a truck driver or not. (LGV = Large Goods Vehicle)
I have a table to store Training course categories, there is 16 in there at the moment. 1 of which is related to truck driving as it to do with safety and fuel efficient driving (ID = 6). A course category can have many employees, and an employee can have many course categories (ie required to be trained).
Because of the many-to-many I've got a bridge table called CourseRequirements just containing employee ID and category ID.
I have a query setup to find all employees that are marked as non-LGV drivers, but the employee requires the safety and fuel efficient driving. This is to identify which employees have wrongly been entered to need the training because they're not a truck driver:
Code:
SELECT tblEmployees.Firstname, tblEmployees.Surname, tblCourseRequirements.CategoryID, tblEmployees.IsLGVDriver
FROM tblEmployees INNER JOIN tblCourseRequirements ON tblEmployees.EmployeeID = tblCourseRequirements.EmployeeID
WHERE (((tblCourseRequirements.CategoryID)=6) AND ((tblEmployees.IsLGVDriver)=False));
What I need now is the opposite of this. To find all truck drivers that are missing the safety and fuel efficient driving requirement from the CourseRequirements table. But I don't quite know how to do this.