If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Double Frustrated Outer Join?
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 |
#2
|
|||
|
|||
Debra Farnham wrote: 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Thank you SOOOOO much for the reference!
I have studied it and followed it and actually managed to implement it. Debra wrote in message ups.com... Debra Farnham wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access Outer Join "Where" Criteria Question | [email protected] | Running & Setting Up Queries | 1 | August 31st, 2005 01:05 AM |
Cartesian product & outer join | Ann Scharpf | Running & Setting Up Queries | 1 | August 25th, 2005 02:22 PM |
MULTIPLE JOINS | leo | Running & Setting Up Queries | 1 | April 7th, 2005 08:35 PM |
Missing Data in an Outer Join | Noah | General Discussion | 1 | August 10th, 2004 04:16 PM |
'Full' outer join syntax | DOTJake | Running & Setting Up Queries | 1 | June 23rd, 2004 08:22 PM |