Report that inludes non-occurence?

[chellelynn77]

Here is a simplified version of what I have:
Employee Table (basic employee information)
Course Table (information about several different classes)
Occurrence Table (each row has employee, class, and date that training occurred)

What I want:
A report by employee that shows the most recent date they took a class (I figured out the query for that) AND indicates if they have not taken the class at all. That is the part I cannot figure out. I don't really care what the space contains (null, blank, etc.) but I want each class to show up under the employee's name whether they have taken the training or not. Is this possible? Do I need to change my setup somehow?



THANKS!

[jzwp11]

Welcome to the forum!

You will need to create a query that yields a Cartesian Product between the employees and the classes. Something like this:

SELECT tblEmployees.pkEmpID, tblEmployees.txtFName, tblCourses.pkCourseID, tblCourses.txtCourse
FROM tblEmployees, tblCourses;

Notice that there are no joins between the two tables, just the two tables

You will now need to create another query. Bring in the query above as well as the query that you created that has the most recent date for each course taken by each employee. Make 2 left joins between the above query and your query. The joins will be between the employeeID and the courseID fields in the two queries.

I have attached an sample database. To illustrate, I just used the occurrence table in place of the query you create, but the principle would be the same.

[chellelynn77]

SOLVED! Thanks so much! Wish I would have found this forum hours/days/weeks/months ago

[jzwp11]

You're welcome! Good luck with your project.