Comparing 2 tables in Access 2000
Posted on 2003-03-18
I hope this is an easy one:
I have 2 tables. The first table is my master database consisting of approx 20,000 records in 25 fields. The second table is a database containing only approx 1000 records in only 3 fields.
What I want to do is to compare the 2 tables (in the form of a re-usable query)and flag those records (in this caee approx 1000 records) where the data IN ALL THREE FIELDS matches.
So in English (as opposed to SQL) the task is roughly: Compare mastertable [Field1] with subtable [field1] AND mastertable [field2] with subtable [field2] AND mastertable [field3] with subtable [field3]. Where all 3 fields match add "ABC123" to [Comments] field and "ZYX987" to [Another] field.
(This comes about because I send out the name, zipcode & fax fields from my whole database to a third party who checks every fax # and then returns