Duplicate Records

[frenchy62]

I want to acknowledge duplicate records from 2 different tables. I want to be able to run the tables against each other and produce a list of the records that ate identical in the 2 tables. I want to omit records from the 2nd table prior to appending them to the 1st table.
Ex. table A is a payee with an established case.
table B is a table I just received with new license from an agency. I want to run table B up against table A,dervive a list of new licensees that I already have a case on. I want to omit these records from table B prior to appending them to table A.

 

[ToeShot]

give me your email and I will send it to you.

Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[humbleprogrammer]

Below are sample queries. Run them in this order. You can first create the queries and then call them behind a command button in a form to automate it.

The first query finds the duplicates and creates a table named "Duplicates" with all the duplicate values.
SELECT TableB.* INTO Duplicates
FROM TableB INNER JOIN TableA ON TableB.payee = TableA.payee;

This query deletes the duplicates from TableB
DELETE TableB.*
FROM TableB INNER JOIN TableA ON TableB.payee = TableA.payee;

This query appends TableB's records into TableA
INSERT INTO TableA ( field1, field2 )
SELECT TableB.field1, TableB.field2
FROM TableB;