Double Frustrated Outer Join?

[Debra Farnham]

Hi All

Access 2000

I've been trying to create a query that will show me the names of all
employees who have completed ALL training topics required by their job
titles.

Background:

I have a table called tblSessions that holds all details with respect to a
training session (fields of interest for the query would be autSessionID and
lngTopicID). There are other fields in the table such as date of training,
time training took place, instructor and length of training.

I have another table called tblTrainingDetails that only contains two
fiields - lngSessionID and lngEmployeeID - both fields used as multiple
primary key to ensure no employee can be added to the same training session
more than once.

I have a third table called tblRequirementsbyPosition that contains only two
fields as well - lngJobTitleID and lngTopicID and a table named
tblEmployees contains the lngJobTitleID field for each employee.

Unfortunately, I cannot see the forest for the trees on this one. I have
created a query that will give me a list of all the requried topics
(actually the lngTopicID) for a specific job titled based on the selectiion
of a job title in a combobox on a form.

SQL:

SELECT tblRequirementsByPosition.lngTopicID
FROM tblRequirementsByPosition
WHERE
(((tblRequirementsByPosition.lngJobTitleID)=[forms]![frmwhosdonewhat]![cboJo
bTitle]));

This will usually return anywhere between 1 and 15 lngTopicID's.

I want to take the results of that query and find all employees who have
completed ALL of those training topics.

It was suggested in another newsgroup that I use a double "frustrated" outer
join and I was provided with the following:

SELECT Main.EmployeeID
FROM tblEmployees AS Main
LEFT JOIN
(
SELECT EmpRequiredTraining.EmployeeID
FROM
(
SELECT E.EmployeeID, RT.[frg_TopicID]
FROM tblEmployees AS E INNER JOIN tblRequiredTraining AS RT
ON E.[frg_JobTitleID] = RT.[JobTitleID]
)
AS EmpRequiredTraining

LEFT JOIN

(
SELECT TD.[frg_EmployeeID], S.[frg_TopicID]
FROM tblTrainingDetails AS TD INNER JOIN tblSessions AS S
ON TD.[frg_SessionID] = S.[SessionID]
)
AS EmpCompletedTraining

ON (EmpRequiredTraining.EmployeeID =
EmpCompletedTraining.[frg_EmployeeID])
AND (EmpRequiredTraining.[frg_TopicID] =
EmpCompletedTraining.[frg_TopicID])
WHERE (EmpCompletedTraining.[frg_EmployeeID] Is Null)
)
AS EmpToBeTrained

ON Main.EmployeeID = EmpToBeTrained.EmployeeID
WHERE (EmpToBeTrained.EmployeeID Is Null)

Unfortunately, I have been unable to implement it and the thread where I
requested additional assistance has gone "cold".

I would be extremely appreciative to anyone with enough patience willing to
help me thru this forest

Debra

 

[peregenem]

I've been trying to create a query that will show me the names of all
employees who have completed ALL training topics required by their job
titles.

Suggested reading:

http://www.dbazine.com/ofinterest/oi-articles/celko1

 

[JoelH]

very good question.
you have to think laterally. i think you need 4 queries for this. you
have to have a list of course requirements for each employee, a list of
courses completed for each employee, and then a list of the entries in
the "requirements" table that doesn't have an entry in the "fulfiled"
table. Let's call this table the "unfilfiled course" table
Employees who have completed all courses are the ones who don't have an
entry in this "unfulfiled course" table

query 1: "qryEmployeesTopicsRequired"
-- list Employees and topics required.

SELECT tblEmployees.lngEmployeeID, tblRequirementsByPosition.lngTopicID
FROM tblEmployees INNER JOIN tblRequirementsByPosition ON
tblEmployees.lngJobTitleID = tblRequirementsByPosition.lngJobTitleID;

query 2: "qryEmployeesTopicsAttended"
-- list Employees and topics attended

SELECT tblTrainingDetails.lngEmployeeID, tblSessions.lngTopicID
FROM tblSessions INNER JOIN tblTrainingDetails ON
tblSessions.lngSessionID = tblTrainingDetails.lngSessionID;

query 3: "qryEmployeesTopicsMissing"
-- this lists entries in EmployeesTopicsRequired that has no
corresponding entry in qryTopicsAttended. In other words, a course
requirement that has not been fulfilled

SELECT qryEmployeesTopicsRequired.lngEmployeeID,
qryEmployeesTopicsRequired.lngTopicID
FROM qryEmployeesTopicsRequired LEFT JOIN qryEmployeesTopicsAttended ON
(qryEmployeesTopicsRequired.lngTopicID =
qryEmployeesTopicsAttended.lngTopicID) AND
(qryEmployeesTopicsRequired.lngEmployeeID =
qryEmployeesTopicsAttended.lngEmployeeID)
WHERE (((qryEmployeesTopicsAttended.lngEmployeeID) Is Null));

query 4: "qryEmployeesCompletedAllTopics"
-- this lists Employees who DON'T appear in the previous query, in
other words Employees who have no pending course requirements to fulfil

SELECT lngEmployeeID FROM tblEmployees
WHERE (((tblEmployees.lngEmployeeID) Not In (select lngEmployeeID from
qryEmployeesTopicsMissing)));


good luck

Joel

 

[Debra Farnham]

Thank you SOOOOO much for the reference!

I have studied it and followed it and actually managed to implement it.

Debra