Austin D Austin D - 4 months ago 9
SQL Question

LEFT JOIN Query not giving expected output

Motivation: I'm designing an access database to track my employee's training status. Each employee has a training requirement based on their role (e.g. admin assistants must take records management training and telephone etiquette whereas electricians must take hazardous safety). I want to be able to generate a report that shows, for each person, all training required, and for those training courses that are completed, the date they were completed.

Schema I have the following tables:


  • Personnel -- lists employees info, e.g. name, telephone, email; key:
    PersonnelID

  • Courses -- lists all courses available and gives a description; key:
    CourseID

  • Roles -- lists all work roles in the company (e.g. electrician, admin assistant); key:
    RoleID

  • LnkPersonnelCourses -- records show that
    PersonnelID
    completed
    CourseID
    on
    CompletionDate

  • LnkPersonnelRole -- records show that
    PersonnelID
    was assigned to or removed from
    RoleID
    with effective date
    EffectiveDate

  • LnkRolesCourses -- records show that
    RoleID
    requires
    CourseID



I created a query
qryPersonnelCourseRequirements
to match
PersonnelID
to the required courses for their current
RoleID
:

SELECT qryLastLnkPersonnelRoleAddOnly.PersonnelID, LnkRolesCourses.CourseID
FROM qryLastLnkPersonnelRoleAddOnly INNER JOIN LnkRolesCourses
ON qryLastLnkPersonnelRoleAddOnly.RoleID = LnkRolesCourses.RoleID;


This works correctly and tells me each individual's current training requirements:

TrainingRequirements

Here is a snapshot of the
LnkPersonnelCourses
table which shows which of the courses each person has completed and when (data is notional).

LnkPersonnelCourses Table

Problem:
When I try to match the individual's training records recorded in
LnkPersonnelCourses
to the training requirements in the above query, it only shows that the individual completed a single course, when in fact, the individual has already completed multiple courses that satsify their training requirements. I've attempted the query several ways. Here's as close as I have gotten:

SELECT pcr.PersonnelID, pcr.CourseID, lpc.CompletionDate
FROM qryPersonnelCourseRequirements AS pcr LEFT JOIN LnkPersonnelCourses AS lpc
ON (pcr.CourseID = lpc.CourseID) AND (pcr.PersonnelID = lpc.ID);


LEFT JOIN with pcr

and

SELECT lpc.PersonnelID, lpc.CourseID, lpc.CompletionDate
FROM qryPersonnelCourseRequirements AS pcr LEFT JOIN LnkPersonnelCourses AS lpc
ON (pcr.CourseID = lpc.CourseID) AND (pcr.PersonnelID = lpc.ID);


LEFT JOIN with lpc

Desired behavior:

I would like the query to produce the following result:

enter image description here

Answer

The issue with both your query was you were comparing PersonnelID of qryPersonnelCourseRequirements table with ID of LnkPersonnelCourses table but you were suppose to compare it with PersonnelID of LnkPersonnelCourses table to get the desire output.

Try this query:

SELECT 
    pcr.PersonnelID, pcr.CourseID, lpc.CompletionDate
FROM 
    qryPersonnelCourseRequirements AS pcr 
LEFT JOIN 
    LnkPersonnelCourses AS lpc On 
    (pcr.CourseID = lpc.CourseID) AND (pcr.PersonnelID = lpc.PersonnelID );

This should produce the desired result.