Help with a query

[Indigo]

I have been trying to figure this one out for several days and I am no further ahead. I am look for help in pointing me in the right direction.

In Access 2010, I have a table with employee information (employee ID, Name, area) and I have a table with jobs (job ID, job name and area). I have built a table that connects which jobs each employee has been certified on (employee ID and job ID). I am trying to build a query that shows me which jobs the employee is not certified on.

I have tried various joins when building the query but I can't seem to figure this one out. Any suggestions / advice would be appreciated. Thank you!:banghead:

Just so you know, I have tried the unmatched query wizard but it doesn't give the correct results. I need to know which jobs by area the TM is not certified on. The following SQL does not provide those results:

SELECT Certified.EmployeeNo, Certified.JobID
FROM Certified LEFT JOIN Job ON Certified.[JobID] = Job.[JobID]
WHERE (((Job.JobID) Is Null));

 

[Ranman256]

make Q1, qsEmpCerts, outer join
join tEmps on tCerts,
show all tEmps, some tCerts

make Q2, qsNoCerts, outer join
using qsEmpCerts and tJobs
show all qsEmpCerts , some tJobs

 

[Indigo]

thank you... that gets me closer...

SELECT Employee.EmployeeNo, Certified.Job
FROM Employee LEFT JOIN Certified ON Employee.EmployeeNo = Certified.EmployeeNo;

this produces 4426 records and all 806 employees.

However, the second query results are unchanged from the first, so I am missing something....

SELECT qryEmployeeCert.EmployeeNo, Job.JobID
FROM qryEmployeeCert LEFT JOIN Process ON qryEmployeeCert.JobID = Job.JobID;