Individuals who have not taken course

[John Menken]

I am using MS Access 2007 and I am trying to produce a list of
managers who have not taken a mandatory instructor led training
course. Here are my two tables:

Table:allABCManagers
ID
FirstName
Lastname
empNo


Table:completedCourse
ID
FirstName
Lastname
empNo
status


The first table, allABCManagers, gives me a complete list of managers
in ABC Company. The last field in this table is empNo which is their
unique employee number.

The second table, completedCourse, is a list of all managers that have
completed the course. The last field, status states “Completed” for
everyone in this table.

How can I run a query to get a listing of everyone that **has not**
taken the course? One other note, sometimes non-managers are allowed
in the course. Therefore their name will appear in the completedCourse
table but that person will not appear in the allABCManagers table.

I'm a newbie to MS Access 2007 and trying to accomplish this in the
Query design window.
Thanks very much.

 

[John Menken]

It only shows me a list of managers that completed the course.
I'm hoping for the opposite, managers who *did not* complete the
course.
Thanks.

 

[John W. Vinson]

It only shows me a list of managers that completed the course.
I'm hoping for the opposite, managers who *did not* complete the
course.
Thanks.

Select the join line in the query design window, and choose option 2 - "Show
all records in allABCManagers and matching records in completedCourse". Put a
criterion of

IS NULL

under the ID field from completedCourse. This "frustrated outer join" query
will find all managers who do NOT have a corresponding record in the
completedCourse table.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Menken]

It Works !!!!
Thank you very much !!!!!