Trying to put together queries through two junction tables cannot figure it out!

[Macawac1]

I am putting together a training Db. My table structure is as follows:





I am trying to pull up a list of people who have not undertaken mandatory training. Mandatory training is dependant upon role, and there is also training that is not mandatory. I have put together a series of queries that pulls up a list of people who have taken the courses and the outcomes, and a list of the mandatory training but cannot figure out how to connect the two. I am starting to doubt my table structure is set up correctly now.

Can anyone advise?

Many thanks,

[ranman256]

make a query to pull those that have taken mandatory training, say: qsTakenMandatory

make a query to pull all Emps and all mandatory classes: qsAllEmpsAllMandatory
this would be the tEmployee table and the tClasses that are mandadory,
DO NOT JOIN THE 2 TABLES.
bring down EmpID, and CourseID. This will combine to show all employees that must take mandatory classes.

now make a new query, add in: qsTakenMandatory and qsAllEmpsAllMandatory
join the 2 queries on EmpID and CourseID
make an OUTER join, dbl-click on the join lines,
set both line properites to ALL RECORDS from qsEmpTraining, some recs from qsAllEmpsAllMandatory
in the query fields, bring in:
qsEmpTraining.EmpID ,qsEmpTraining.ClassID, qsAllEmpsAllMandatory.ClassID

if you run this, you should get all records , taken and not taken.
under qsEmpTraining.ClassID, set criteria to IS NULL.
and you should only see Emps who have NOT taken the mandatory class

[isladogs]

I think you've probably 'over normalised' your tables in the structure shown

Ranman's answer with the Cartesian join doesn't take into account the employee role related to the courses required

Here's what I would do
1. qryEmpMandTraining - courses each employee should attend

SELECT tblEmployees.EmployeeID, tblEmployees.RoleID, tblEmployees.FirstName, tblEmployees.LastName, tblEmployees.StartDate, tblMandatoryTraining.CourseIDFROM (tblMandatoryTraining INNER JOIN tblRoles ON tblMandatoryTraining.RoleID = tblRoles.RoleID) INNER JOIN tblEmployees ON tblRoles.RoleID = tblEmployees.RoleID;

2, qryEmpCourseAttendance - courses each has attended

SELECT tblCourse.CourseID, tblCourse.Course, tblCourseDetails.StartDate, tblCourseAttendance.EmployeeID
FROM (tblCourseDetails INNER JOIN tblCourse ON tblCourseDetails.CourseID = tblCourse.CourseID) INNER JOIN tblCourseAttendance ON tblCourseDetails.CourseID = tblCourseAttendance.CourseDetailsID;

3. Unmatched query qryEmpMissingTraining - linking both of these together &showing employees with missing courses

SELECT qryEmpMandTraining.EmployeeID, qryEmpMandTraining.RoleID, qryEmpMandTraining.FirstName, qryEmpMandTraining.LastName, qryEmpMandTraining.StartDate, qryEmpMandTraining.CourseIDFROM qryEmpMandTraining LEFT JOIN qryEmpCourseAttendance ON qryEmpMandTraining.EmployeeID = qryEmpCourseAttendance.EmployeeID
WHERE (((qryEmpMandTraining.StartDate)<[qryEmpCourseAttendance].[StartDate]) AND ((qryEmpCourseAttendance.EmployeeID) Is Null));

NOTE: I've also added a check to only include records where employee start date was before a course was run
A further complication is the Refresher Period - but I'll leave that to you

Attached is an empty database with these items
To save time, I've omitted any fields that aren't used in the above queries

EmpTraining.zip

[Macawac1]

Hi Ranman and Ridders,

Thank you both very much for helping me out :-)

Ridders - Could I ask what I could do to not "over normalise"? (Apologies for any ignorance shown here).

Regards

[Macawac1]

Hi again,

I have constructed the queries in the way that you have suggested Ridders - however the final query is not pulling up the information I would expect - it is doing exactly what it has been doing all morning when I have been trying to figure it out!

With the practice data that I have entered we have 12 mandatory courses (taking account of role/course combinations) that should be taken - 7 of which have been, leaving 5 still outstanding. However when running the final query with the "is null" left out for now I see 26 results and not the 12 I would expect to see (with the "is null" in place I would expect to see just the 5 outstanding).

What am I doing wrong?

[isladogs]

One suggestion is that you have too many joins in your table relationships
However, I'd need to see your data to know why it didn't work correctly

In the meantime, try changing the queries from SELECT to SELECT DISTINCT to only pull up unique records
If that doesn't fix it, suggest you post your database?

Many forum users are better than me at spotting structural issues in database design e.g. ssanfu
However, one suggestion would be that tblRoles could probably be eliminated - just add Role field to tblEmployees.

[Macawac1]

Thank you again for your help

It is now working wonderfully!!!! I got rid of all the practice data and started again with just a few simple records and it works!! I obviously messed up my practice data entry with all the various IDs and mis-entered some into the tables. I would have been there all day on my own trying to figure it out by redesigning my queries but I had faith that yours were correct and so came to the conclusion my data entry had gone to pot!!

Thank you again

[isladogs]

You're welcome. Glad you got it working.