Unmatched Query

[Guest]

I created an “Find Unmatched†Query – comparing a table and a query and
everything worked out fine. Then I created another “Find Unmatched†Query
comparing another table and query and it was a mess. The table intially had
1405 records, the query had 1348 records – Then I ran
the query and the total number of unmatched was 39,826. I noticed that it
duplicated A LOT of records – Why would this happen?

 

[Tom Wickerath]

It sounds like you have produced a Cartesian Product result. If you are missing a join between
two tables in a query, your recordset will include n x m records, where n represents the number
of records selected from one table and m represents the number of records selected from the other
table.

As an example of a Cartesian Product, open the sample Northwind database. Open the Alphabetical
List of Products query and you should see 69 records returned. Now open this query in design
view. Click on File > Save As... and give it a new name, such as Alphabetical List of Products2.
Select the join line that ties the CategoryID fields together. Press the delete key to delete
just the join. Now re-run this query. Note that instead of 69 records, you all of a sudden will
have 552 records. This represents 69 records selected from the Products table multiplied by 8
records selected from the Categories table.


Tom
__________________________________


I created an “Find Unmatched†Query – comparing a table and a query and
everything worked out fine. Then I created another “Find Unmatched†Query
comparing another table and query and it was a mess. The table intially had
1405 records, the query had 1348 records – Then I ran
the query and the total number of unmatched was 39,826. I noticed that it
duplicated A LOT of records – Why would this happen?