Recon Query

[Guest]

Okay.. I'm going to try and simplify my problem because I'm really looking
for a conceptual method to do what I'm doing.

It's basically a recon of 2 files with the same fields. Let's say there are
10 fields. The criteria for a match consists of matching 4 of the fields.
For that I've made a 'virtual' key (Field_1 & Field_2 & Field_3 & Field_4)

I need to produce the following
- all records that match
- all records in table 1 that are not in table 2
- all records in table 2 that are not in table 1

It seemed to be easy to start with. I did a 'inner' (default) query for the
matches.
A 'left' join and NOT in matched for recs in T1 not T2.
vise versa for recs in T2 not T1

Now here's the problem..
The records from the files don't necessarily have uniqueness. That is, even
if I used all 10 fields, there may still be duplicate rows. (* and should be,
think of it as an order... Cust A orders 100 units of product B etc)

So if I have 4 records in T1 that have a 'key' of A and 2 records in T2 that
have a key of A. What I should get is 2 records in a matched table and 2
records in the view that shows recs in T1 not in T2.

What I GET is 8 records in the matched table (the join is doing a product)
and NO records in either of the other views.

I'm guessin I'll probably have to write code that will 'remove' a row from
each table once they match so they can't be used again.

If anyone has any ides on how to do this that would be GREAT!!

 

[Guest]

I would create one query that return all the records in table1 that are not
in table2
SELECT MyTable1.*
FROM MyTable2 RIGHT JOIN MyTable1 ON MyTable2.Field_Name = MyTable1.Field_Name
WHERE MyTable2.Field_Name Is Null

And now create a second query, union query, that include all the records
from table2 and the records from the above query

Select * From Table2
Union
Select * From QueryName