Intersection of tables

[John]

Hi

How can I find intersection of two tables i.e. records in one table which
are not in the second table while matching values in two fields
simultaneously in each table i.e. matching table1.id1 and table1.id2 with
table2.id1 and table2.id2 respectively?

Thanks

Regards

 

[Steve]

Open to the database window and go to queries. Click on New and select
UnMatched Query. See if that will work for you.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Dirk Goldgar]

In

How can I find intersection of two tables i.e. records in one table
which are not in the second table while matching values in two fields
simultaneously in each table i.e. matching table1.id1 and table1.id2
with table2.id1 and table2.id2 respectively?

Steve's suggestion of using the query wizard is good, but unfortunately
the query wizard won't build this query for more than one pair of
matching fields. You could use the query wizard with one pair of
fields, then look at the SQL of the resulting query and extend it to
include the second pair of fields.

Your query will look something like this:

SELECT Table1.*
FROM
Table1
LEFT JOIN
Table2
ON
Table1.id1 = Table2.id1 AND
Table1.id2 = Table2.id2
WHERE
Table2.id1 Is Null;

Incidentally, this is not what I would call the "intersection" of the
tables. As I was taught it, the intersection of the two sets is the
members that are common to both, so that would be the records that
result from an inner join, not an outer join like the above.

 

[John]

Unmatched query only compares on one field form each table as far as I can
see? I need it to compare on two fields from each table simultaneously.

Thanks

Regards

 

[Steve]

Create a query for each table. In each query, create a calculate field:
ComparisonField:[NameOfField1] & " " & [NameOfFiled2]
This combines both fields you want to compare into one field. Now use the
unmatched query wizard on both queries.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)