MS Access SQL Employee Training Database
I have a Microsoft Access database which is used to keep track of employee training.
The database has three tables:
tblEmployees(EmployeeID, Name, Function) tblTraining_Courses(CourseID, Title, Desc, Function) tblTraining_Records(EmployeeID, CourseID, Date, Status)
EmployeeID is their personnel number
Name is the Employee's name
Function is the product line the employee supports - Electric, Lighting or Both (Multivalue Lookup)
CourseID is the courses's ID number
Status is whether the course has been completed
I am trying to query the "tblTraining_Records" table to see which employees have not completed a training course.
I followed another example posted here and it works really well: Stackoverflow: MS Access SQL Course not Taken Example
However, I also want to filter that query based on whether the employees function matches the courses function. The function is a multivalue lookup field with three possible choices Electrical, Lighting or Both. Courses can also be for just Electrical, Lighting or Both.
Here is the SQL I have for my query.
SELECT nested.EmployeeID, nested.Course_ID FROM (SELECT distinct EmployeeID, Course_ID FROM tblEmployees, tblTraining_Courses) nested LEFT JOIN tblTraining_Records r ON r.EmployeeID = nested.EmployeeID AND r.Course_ID = nested.Course_ID WHERE r.Employee is NULL;
Any assistance would be greatly appreciated. Thank you
Join courses with with nested table and then add a new condition in where clause as following:
SELECT nested.EmployeeID, nested.Course_ID FROM (SELECT distinct EmployeeID, Course_ID FROM tblEmployees, tblTraining_Courses) nested LEFT JOIN tblTraining_Records r ON r.EmployeeID = nested.EmployeeID AND r.Course_ID = nested.Course_ID inner join tblTraining_Courses tc on nested.course_ID=tc.CourseID WHERE r.Employee is NULL and tc.Title IN ('Electrical', 'Lighting');
I will add this approach: (lets call this Query1) When you join all 3 tables, and have all the fields in the query - you'll have 2 fields named Function. This isn't allowed so let's alias that to eFunction for employee and cFunction for course.
Then create a calculated field: Completed: iif(cFunction = eFunction, "Yes", Null)
then you can filter Query1 on Yes