Leaving only unique records or deleting ALL duplicates

[TSW]

Maybe I've been just thinking about this to hard......I have 2 tables with
that contain a document number. Table 1 is to be the new updated table and
table 2 have older the information. Some of the records that have been
created in table 1 already exist in table 2 but I want to make sure that all
of the records in table 2 are represented in table 1. How can I filter out
the duplicate record and leave only record that have not been updated.
example Table 1 Table 2
1 1
2 2
2 4
3 3
3 6

I would like to be left with (although it dosent have to be a new table)
Table 3
4
6

Thanks

 

[Jeff Boyce]

NOTE: before running queries that alter your data, make sure you have a
backup!

If you have a unique index on that "document number" field, you could append
from Table2 to Table1. Access will reject the Table2 records that would
match existing Table1 records.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Roger Carlson]

Try this:

DELETE *
FROM Table2
WHERE RecordID IN (SELECT RecordID FROM Table1);

RecordID should be replaced with the unique identifier (primarykey) in both
tables.

NOTE: make a backup first.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L