Comparing 2 tables (1 Viewer)

[IceGirlAnne]

Hi! I am a super-novice user of Access and I was wondering if you could possibly help me with my problem (I'm using Access 97)---

I have imported data from 2 files into 2 Access tables. Each one has the same fields--Product Number, Source Location number, destination location number, ship date, arrive date, and quantity.

In the tables, the same "product number" is there numerous times in the table and each record has the same "source location" and each has the same "destination location" for both tables. (So, nothing unique to distinguish one record from another with just the product number, source location, and destination location--in case you need to know that)

What I want to do is compare both of these tables and show any differences between them. So basically, when I look at the "product number", the "source location number" and the "destination location number" in one table and compare it to the other table, if there are differences in the rest of the fields, I would like both of those records shown.

I don't have any primary keys or relationships set up and I don't know if I'm supposed to do that or not for this task.

I thought maybe a wizard could help--like the "Finding Unmatched Query Wizard" but so far my experience with that has been that it only shows unmatched records if it has like a particular unique key to compare each table to. I don't know!

If you have any ideas, please let me know. I greatly appreciate any help you have! Thanks so much for your time and help!!!

 

[RV]

You could use the Finding Unmatched Query Wizard as a base:

>so basically, when I look at the "product number", the "source location number" and the "destination location number" in one table and compare it to the other table, if there are differences in the rest of the fields, I would like both of those records shown<

so for your purpose you could consider
the combination of "product number", "source location number" and "destination location number" as a combined unique key.

Adapt the query made by the Wizard to this key.

An advice; don't use column names with spaces in it.

RV

 

[Pat Hartman]

The wizard isn't going to help you in this case since it will only identify "missing" keys. You'll need to create your own query.

Join the two tables on all three fields. Select all the fields from table1 and all but the key fields from table2. Then in the criteria field for each non-key field of table1 for which you want to check for differences put -
<> table2.[coorespondingfieldname]

Put each of these conditions on a separate line in the criteria grid so the generated SQL statement will connect them with OR. If you put them all on the same line, Access will connect the conditions with AND and you will not get the desired results.