Intersection of tables

J

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
 
S

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)
 
D

Dirk Goldgar

In
John said:
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.
 
J

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
 
S

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top