Finding Unmatched records from two tables

[Guest]

I have two tables where all records should match. One table, however, has
more records than the other and I haven't been able to determine why.
Unfortunately, the records are such that there is no primary key and there is
no one field that has unique data. All fields combined make each records
unique. How do I determine which records in one table are not in the other?

 

[Guest]

Have you tried the Find Unmatched Query Wizard (select it when you create a
new query)?

 

[Guest]

Tia,
I'm sorry- I'm just not that good at the Access thing.
Maybe a more technical person will answer.

 

[Guest]

Yes, I tried the query but the problem is that no one field is unmatched due
to the nature of the fields. the only way to determine if it is unmatched is
to compare all fields in a record in Table 1 to all fields in a redord in
Table 2. Is that possible to do?

 

[John Spencer (MVP)]

How many fields are involved? IF just a few you can make your own unmatched query.

--Add Both tables to the query
--Create a join line between each set of fields (TableOne.FieldA to TableTwo.FieldA)
--Click on the join line and select the Option All records in tableOne (this
assumes tableOne has the most records) and only matches in table two.
Repeat for the other tables

Add all the fields to the grid.
Put IS NULL in the first criteria line under each of the fields in TableTwo.

If there aren't to many fields involved this should work.

If not, post back. And we can try a UNION ALL Query as the source for a totals
query. =Hopefully you have at least one field in each of your two tables that
always has a value.