I have two different tables that contain lists of names and addresses (the tables have to remain seperate). I want to run a query that will check both tables for duplicate records.
The criteria I use would be surname, first line of address and postcode.
I want to then move these duplicate records into a new table
Any ideas?

(using Access 97)

Look here:
http://www.access-programmers.co.uk/ubb/Forum3/HTML/003009.html

Then make a new table based on the "duplicate records" query.

Why using to different tables with the same data?

Why do you want to store the duplicate records in a separate table?

Advice in both cases: don't, there never a reason to store same (kind of) data in separate tables.....

RV

The only reason I can conceive of to store duplicated records in yet a third table is if you're finding people in two unrelated databases, i.e. all the people who both read Popular Science and Computer Shopper, and you have access to both readership databases but cannot modify them.

Otherwise, yes, storing duplicate data in different places is almost never necessary. Can you tell us more about what your eventual goal is?

David R

The data comes in from a data supplier, it is then compared to the data in our mailing databases to make sure that we are not getting data supplied that we already have. This is the two tables.

Any duplicated data is then returned to the data supplier (we are then refunded for that duplicated data) This is why I need to create a third table for duplicated data.

The "clean" data from the table of supplied data is then imported into the (main) mailer database and the supplied table is cleared of records so we only have one set of data.

hope this makes more sence now

cheers

Makes good sense.

As you can tell, sometimes there IS reason to duplicate data. Absolute truths are rarely present outside of the drawing room.

David "Zen" R