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.

 

[Marshall Barton]

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.

Add both tables to the query. If it's not added
automatically, drag the empno field from one table to the
other empno field in the completed table to create a join
line between the tables. Double click on the line and
select the only matching records in both tables.

Now add the fields from the managers table to the query's
field list and then test it to see if the query provides the
desired results.

 

[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 !!!!!