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

 

[StopThisAdvertising]

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

--
This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3300+ pageloads, 2200+ first-time visitors (these figures are rapidly increasing)

To PCD' Steve: (this is also to inform those who do not understand...)
This is *not* about the sigline...(although you are far away from a 'resource' status).
But we will simply continue to hunt down *each and every* of your posts.

Why???
Because you are the ONLY person here who continues to advertise in the groups.

It is not relevant whether you advertised in *this* particular post or not...
==> We want you to know that these groups are *not* your private hunting grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR

 

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