Excluding Data from a table using a query

[Guest]

Hi...

I have created a database with 3 tables, and I can compare data in 2 of the
tables to find any data mismatches and then report them. However, in my 3rd
table, I have a list of employee numbers that I wish to be ignored.

How do i get my query for comparing the first 2 tables, to ignore any
employees that appear in my 3rd.

Thanks :O)

 

[Guest]

Try this --
In design view add the third table but do not join.

Use the third table as criteria like this --
<>[ThirdTable].[EmployeeNumber]

 

[Guest]

Add a subquery based on the third table to the outer queries WHERE clause e.g.

WHERE NOT EXISTS
(SELECT *
FROM Table3
WHERE Table3.EmployeeNumber = Table1.EmployeeNumber)

Ken Sheridan
Stafford, England

 

[John Vinson]

Hi...

I have created a database with 3 tables, and I can compare data in 2 of the
tables to find any data mismatches and then report them. However, in my 3rd
table, I have a list of employee numbers that I wish to be ignored.

How do i get my query for comparing the first 2 tables, to ignore any
employees that appear in my 3rd.

Thanks :O)

Sorry - replied to the previous message too soon, didn't realize you
had these in a table.

Karl's answer won't work unfortunately; it will return all employees
(since all of them are going to be unequal to at least one of the
list). Two ways that will work:

1. Use a criterion of

NOT IN(SELECT EmployeeID FROM TableToBeIgnored)

on the EmployeeID;

2. Join TableToBeIgnored to your main table using a Left Outer Join
and use a criterion of IS NULL on the join field.

John W. Vinson[MVP]