Not Trained Query

[Waddy]

Hi All,

I have a query that shows what training an employee has, how would I go about showing what training he/she does not have in a query.

(Indicates Primary Key On Each Table)

I have a list of training courses in a table called tblCourses (CourseID)
I have a table called employees called tblEmployees (OracleNo)
And I have a table were the information is stored called tblTraining (OracleNo)

On a query I just used the tblTraining to get what he/she is trained in but how would I approach not trained in.

I hope you can help, like you always have

 

[arnelgp]

to show a list that courses that an employee has not taken:

select tblcourse.courseid from tblcourse where tblcourse.courseid not in (select tblemployee.courseid from tblemployee where tblemployee.empid="theEmployeeIDhere");

 

[plog]

First, you need a list of every Course/Employee permutation. THis query will do that:

SELECT CourseID, OracleNo
FROM tblCourses, tblEmployees

Paste that SQL into a new query and call it 'sub_MissingTraining'. Then, from that subtract out all Course/Employees in tblTraining and you will get your required data. This subtraction can be achieved with a LEFT JOIN (showing all from sub_MissingTraining) and applying Null criteria on tblTraining. This is that SQL:

SELECT sub_MissingTraining.CourseID, sub_MissingTraining.OracleNo
FROM sub_MissingTraining
LEFT JOIN tblTraining ON sub_MissingTraning.CourseID = tblTraining.CourseID
  AND sub_MissingTraning.OracleNo = tblTraining.OracleNo
WHERE tblTraining.OracleNo IS NULL