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

2 answers

  • answered 2019-01-11 05:53 Muhammad Waheed

    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');
    
    

  • answered 2019-01-11 13:05 Cahaba Data

    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