Query output to include matching & missing information

[mst3kr]

I have a employee training database that I am trying to create a single query that shows who has and hasn't taken the training for a certain department & month, but I keep coming up short.

The 1st table is called Required. This has training class name, training class #, and the employee position that needs to take the training.
The 2nd table is called Calendar. This has department name, training class name, training class #, and the month the training is to be completed in.
The 3rd table is called Records. This has training class name, training class #, employee, and date the employee took the training.

What I need is a query that shows me a list of all people who are supposed to take a specific training class, from a specific department & training class, where they've taken the training or not. Below is an example the query/report of 4 people who are required to take a class. I need the output to look something like this. I've tried various make table queries, unmatched queries, some VBA, and and other tricks to try and make it happen but to no avail. I'm sure I'm missing something simple but have run into a dead end to make it work. Any help?

Department Class Employee Date
Sanitation Cleaning John Smith 8/1/19
Sanitation Cleaning Jane Doe Not Taken
Sanitation Cleaning Mike Brown 4/1/19
Sanitation Cleaning Mary Jones Not Taken

[June7]

Is there a table of Employees that would have the position each employee assigned to?

If you want to provide db for analysis, follow instructions at bottom of my post.

[mst3kr]

Is there a table of Employees that would have the position each employee assigned to?

If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks for the reply! Yes, there is a table called Employees that has each employee and their job title. I've attached the database for your review. This is a database I found online from elsmar.com that I've been able to adapt. This output just happens to be a sticking point for me...

https://elsmar.com/elsmarqualityforum/threads/employee-training-database-wanted.33075/

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[June7]

This database has a lot of formatting set in tables such as lookups (comboboxes), alias (Caption) field names, input mask, etc. I NEVER do this in tables.

Instead of saving descriptive text for job title, would recommend autonumber PK in tblJobs and save that as FK in tblEmployees and tblRequired.

Try:

Query1:
SELECT tblEmployees.strEmpNum, tblEmployees.strLastName, tblEmployees.strFirstName, tblRequired.strProcNum, tblRequired.strProcRev
FROM tblRequired INNER JOIN tblEmployees ON tblRequired.strDept = tblEmployees.strDept;

Query2:
SELECT Query1.strEmpNum, Query1.strLastName, Query1.strFirstName, Query1.strProcNum, Query1.strProcRev, tblRecords.dtmDate
FROM tblRecords RIGHT JOIN Query1 ON (tblRecords.strProcNum = Query1.strProcNum) AND (tblRecords.strProcRev = Query1.strProcRev) AND (tblRecords.strEmpNum = Query1.strEmpNum);

All in one:
SELECT Query1.strEmpNum, Query1.strLastName, Query1.strFirstName, Query1.strProcNum, Query1.strProcRev, tblRecords.dtmDate
FROM tblRecords RIGHT JOIN (SELECT tblEmployees.strEmpNum, tblEmployees.strLastName, tblEmployees.strFirstName, tblRequired.strProcNum, tblRequired.strProcRev
FROM tblRequired INNER JOIN tblEmployees ON tblRequired.strDept = tblEmployees.strDept) As Query1 ON (tblRecords.strProcNum = Query1.strProcNum) AND (tblRecords.strProcRev = Query1.strProcRev) AND (tblRecords.strEmpNum = Query1.strEmpNum);

[mst3kr]

Is there a table of Employees that would have the position each employee assigned to?

If you want to provide db for analysis, follow instructions at bottom of my post.

Your directions worked perfectly! Thank you! I knew I was missing a couple of easy steps to make the query worked like I wanted...

I will also look at your autonumber suggestion. With so much code and creation in the database before I got my hands on it, I've been hesitant to make too many tweaks to the original design in case it caused any failures down the road. However, it seems like it would be much simper in the long run.

Thanks again!