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");

 

[Pat Hartman]

When you don't know how to build a query, the QBE can be very helpful. This is a typical find unmatched query. Try running the wizard and see if that gets what you want. Then switch the query to SQL View to examine the generated SQL string. You will find that it joins the tables with a left join and looks for training where the courseID is null (i.e. not found).

 

[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