Comparing tables to locate zero values

[Guest]

I have a table named "tblAllFiles" that contains a col named "Salesman".
Every record will have a salesman assigned to it in the tblAllFiles table.
Then I have another table named "tblStaff" with "Salesman", "Secondary
Salesman" and Primary Salesman" columns. I need to compare these two tables
and identify records in the "tblAllFiles" where the "Salesman" name is not in
the "tblStaff" table. How can I do this easily. We add new salesman and I
need to be sure that the tables are always in synch. Thank you.

 

[Guest]

You can use the query wizard to create an UnMatch record query to list all
the records that are in one table but not in the other

 

[Guest]

I've dont' know....I've never heard of Unmatch query...can you step me though
that. I appreciate your help, thank you.

 

[Guest]

The best way to do it (which means not easy). Break out or redesign your
tblStaff. It should not have individual "Salesman", "Secondary Salesman" and
"Primary Salesman" columns. Rather it should have either a TypeSaleman column
with Salesman", "Secondary Salesman" and "Primary Salesman" as the valid
entries.

If a saleman can be different types, you may even need another table.

Next you would open the relationship window and join the tblAllFiles and
tblStaff tables on the appropiate primany and foriegn key fields. Next
enforce Referiental Integrity so that it is impossible to have a salesman in
tblAllFiles without a match in tblStaff.

Now the "easy" way to find out where you have 'orphans'. Note that this
tells you about problems and doesn't prevent them from happening again.
Create a query as follows and name it "qrySalemanMissing":

SELECT Salesman
FROM tblAllFiles
WHERE Salesman
NOT IN (SELECT Salesman FROM
(SELECT Salesman
FROM tblStaff
WHERE Salesman Is Not Null
UNION
SELECT [Secondary Salesman]
FROM tblStaff
WHERE [Secondary Salesman] Is Not Null
UNION
SELECT [Primary Salesman]
FROM tblStaff
WHERE [Primary Salesman] Is Not Null )) ;

 

[Guest]

When you create a query, you can select query wizard.
One of the options is UnMach Record query

Try this SQL, hopefuly the names are correct

SELECT tblAllFiles.Salesman
FROM (tblAllFiles LEFT JOIN tblStaff ON tblAllFiles.Salesman =
tblStaff.[Primary Salesman]) LEFT JOIN tblStaff AS tblStaff_1 ON
tblAllFiles.Salesman = tblStaff_1.[Secondary Salesman]
WHERE tblStaff.[Primary Salesman] Is Null
AND tblStaff_1.[Secondary Salesman] Is Null