find unmatched when comparing multiple fields

[LxQ]

I have tables that have similar data and I'd like to find unmatched records based on combinations of fields. For start, let's say I have two tables, each of them having a class_id and a mod_id field as in the following example:

table1
class_id mod_id
class001 mod001
class001 mod002
class002 mod001

table2
class_id mod_id
class001 mod001
class001 mod003

So far, the combination of class001-mod001 combination is in both tables, but the others aren't. I'd like to get a query of the unmatched combinations, or even better, one that shows both what's matching and what's not.

thanks in advance!

 

[Joe4]

The simplest way to do this is in a series of three queries.

1. Matching records
Simply create a query, joining your two tables on BOTH the class_id and mod_id fields, and return the fields you want to see.

2. Records in Table1 not in Table2
Create an Unmatched Query between Table1 and Table2.
If you do not know how to do this, there is an Unmatched Query Wizard that will ALMOST get you there.
Go through the Wizard. You will only be able to JOIN the two tables on one field in the Query. So, just pick one of the fields.
When finished, open up the Query in Design View. You will see the LEFT JOIN, which shows an arrow in joining the field between the two tables.
Now, click and drag from the other field you want to join from one table to the other. It will then ask what kind of join you want. Choose option 2 or 3.
When done, both arrows should be pointing in the same direction. If not, double-click on the Join you just created, and change from option 2 to option 3 (or vice versa).
This will give you want you want.

3. Records in Table2 not in Table1
Same thing as number 2 above, just going in the opposite direction.

These should give you what you want.

 

[LxQ]

Thank you,

I got the matched records and I almost got the unmatched records. I got as far as having the two lines but only one of them had the arrow point.. now it's good.

 

[Joe4]

Double-click on the line with no arrows, and it will give you the three JOIN options.
It will be on the first option, and you want the 2nd or 3rd (whatever one ends up pointing in the same direction as the other one).

 

[Leandroarb]

Hi, see:

SELECT Tabela1.[class_id mod_id]
FROM Tabela1 LEFT JOIN Tabela2 ON Tabela1.[class_id mod_id] = Tabela2.[class_id mod_id]
WHERE (((Tabela2.[class_id mod_id]) Is Null));