SQL Code for Query to find what classes have not been taken

[rnc110]

Ok this is going to be very hard to explain without the database. What I want to do is see which employees do not meet the required training classes and list the employee name along with the class(es) they have not completed. For some reason this is driving me crazy. I can not write the right SQL to do this I have four tables.

Table1 is empEmployee with attributes of:EmpID_num, EmpLName, EmpFName, Position_Num(which is the job position in the company ex. 1 could stand for MIS) Ttable is just the employee information

Table2 is tblTrainingCompleted with the attributes of Training_Num (just an autonumber), EmpID_Num (which Employee took the class), Course_Num (Which course was taken), Date. This table keeps track of what class a certain employee took on a certain date

Table3 is tblAvailClasses with the attributes of Course_Num, and Description (Just a descrition of what the course number is) This table is like a catalog of all classes availiable.

Table 4 is tblPosition with the attributes of: Postion_Num (this is the postion they hold is the company just like in the tbleEmployee), Position_Title ( just a description of the Position), then Training_Needed1, Training_Needed2, Training _Needed3 , Training_Needed4 (Which are the appropriate classes that they must be trained in this would be the course_num. This table is just a catalog of all the poitions in the company and the Training needed in that position. For ex. Position_num could be 1 with Title being MIS then classes need could be class 1, 5, and 7

 

[tlbroadbent]

This query is one way to get what you want.


Select a.EmpID_Num, a.EmpLName, a.EmpFName, a.Position_Num, c.Course_Num, c.Description
From empEmployee a
Inner Join tblPosition b
On a. Position_Num=b. Position_Num
Inner JointblAvailClasses c On b.Training_Needed1=c. c.Course_Num
Where Not Exists
(Select * From tblTrainingCompleted
Where EmpID_Num=a. EmpID_Num
And Course_Num= b.Training_Needed1)
Union
Select a.EmpID_Num, a.EmpLName, a.EmpFName, a.Position_Num, c.Course_Num, c.Description
From empEmployee a
Inner Join tblPosition b
On a. Position_Num=b. Position_Num
Inner JointblAvailClasses c On b.Training_Needed2=c. c.Course_Num
Where Not Exists
(Select * From tblTrainingCompleted
Where EmpID_Num=a. EmpID_Num
And Course_Num= b.Training_Needed2)
Union
Select a.EmpID_Num, a.EmpLName, a.EmpFName, a.Position_Num, c.Course_Num, c.Description
From empEmployee a
Inner Join tblPosition b
On a. Position_Num=b. Position_Num
Inner JointblAvailClasses c On b.Training_Needed3=c. c.Course_Num
Where Not Exists
(Select * From tblTrainingCompleted
Where EmpID_Num=a. EmpID_Num
And Course_Num= b.Training_Needed3)
Union
Select a.EmpID_Num, a.EmpLName, a.EmpFName, a.Position_Num, c.Course_Num, c.Description
From empEmployee a
Inner Join tblPosition b
On a. Position_Num=b. Position_Num
Inner JointblAvailClasses c On b.Training_Needed4=c. c.Course_Num
Where Not Exists
(Select * From tblTrainingCompleted
Where EmpID_Num=a. EmpID_Num
And Course_Num= b.Training_Needed4)
Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[MichaelRed]

Well, to attack the issue stringht on.

Your db is lacking some elements of normalization.

Wasn't to bad until you got to:

Table4 (tblPosition)
Training_Needed1, Training_Needed2, ... .

These should all fit comfortably in a single field, repeated as necessary for the requirements of the position.

In Your example, you would have three records for position 1:


Position[tab][tab]Trainning_Needed
[tab]1[tab][tab][tab][tab]1
[tab]1[tab][tab][tab][tab]5
[tab]1[tab][tab][tab][tab]7






MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Sorry, went to preview to see if the 'fields' lined up. Clicked submit vs. Edit. So, then to continue.

IF (when) the table is 'normalized', the query become a lot more straightforward. I won't delve into that, because it probably isn't as issue.

After this is straightened out, you need to go further w/ the db. Almost all Corporate enforced training programs not only require the trainning, but require that it be accomplished within a specific time frame of achieving the position (wheather thru hire or promotion). So, you need a date of attaining the position and a date for the required completion of the training.

Next, these Corportate mandated exercises, again, require 'refresher' training on a periodic basis - with !!!SUPPISE !!! required intervals between the 'refreshments'!!

So, now you need some additional fields to hold or determine when the refresher courses are required (or perhaps overdue). In some prefessional circles, it is possible to loose the 'accrediation' to perform certain jobs if the trainning is not up to date.

There are A LOT more elements to deal with in the process, but I thought I would suggest these few and let you cotemplate these few items.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over