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 )) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"SITCFanTN" wrote:
> 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.
|
Similar Threads
