excluding records based on a table

[Peter]

Hello everyone,

I have two tables that contain the same information concerning two columns
in each table. But not quite the same.

The idea is to have a query that compairs the two tables to each other and
that query results in only those records that are in the first table but
which are not in the second table. This turns out to be easy if it involves
compairing only one column in the first table to only one column in the
second table. The situation I'm working on, however, involves compairing a
combination of two columns in the first table (which make up the combined
primary key for that table) to the same two columns in the second table.
For completeness, I will mention that the primary key of Table2 consists of
both columns of the primary key in Table1 plus yet another field, which I
shall call AddedNumber. This number is irrellevant when it comes to
compairing the first two fields in both tables to eachother.

I managed to get a sample on this subject from a forum and applied it to the
before mentioned one-to-one column comparison. Again, this was quite easy.
Now here is a part of the tables from which I would like a similar result.

Table1:
MainNumber FollowUpNumber
31268 1
32371 1
32372 1
32418 1
32487 1
32501 1 (left out by the query)
32501 2
39700 1
39700 2
39700 3
75840 1 (left out by the query)
75840 2

Table2:
MainNumber FollowUpNumber
32501 1
75840 1

Therefore the result of the query ought to be
MainNumber FollowUpNumber
31268 1
32371 1
32372 1
32418 1
32487 1
32501 2
39700 1
39700 2
39700 3
75840 2

I can't get it to work, because it propably involves more than one "inner
joins". Can anyone help me on this one?

Greetings,
Peter

 

[XPS350]

Hello everyone,

I have two tables that contain the same information concerning two columns
in each table. But not quite the same.

The idea is to have a query that compairs the two tables to each other and
that query results in only those records that are in the first table but
which are not in the second table. This turns out to be easy if it involves
compairing only one column in the first table to only one column in the
second table. The situation I'm working on, however, involves compairing a
combination of two columns in the first table (which make up the combined
primary key for that table) to the same two columns in the second table.
For completeness, I will mention that the primary key of Table2 consists of
both columns of the  primary key in Table1 plus yet another field, which I
shall call AddedNumber. This number is irrellevant when it comes to
compairing the first two fields in both tables to eachother.

I managed to get a sample on this subject from a forum and applied it to the
before mentioned one-to-one column comparison. Again, this was quite easy..
Now here is a part of the tables from which I would like a similar result..

Table1:
MainNumber     FollowUpNumber
31268                   1
32371                   1
32372                   1
32418                   1
32487                   1
32501                   1 (left out by the query)
32501                   2
39700                   1
39700                   2
39700                   3
75840                   1 (left out by the query)
75840                   2

Table2:
MainNumber     FollowUpNumber
32501                   1
75840                   1

Therefore the result of the query ought to be
MainNumber     FollowUpNumber
31268                   1
32371                   1
32372                   1
32418                   1
32487                   1
32501                   2
39700                   1
39700                   2
39700                   3
75840                   2

I can't get it to work, because it propably involves more than one "inner
joins". Can anyone help me on this one?

Greetings,
Peter

I think your query should look like:

SELECT T1.MainNumber, T1.FollowUpNumber
FROM T1 LEFT JOIN T2 ON
(T1.FollowUpNumber = T2.FollowUpNumber) AND (T1.MainNumber =
T2.MainNumber)
WHERE T2.MainNumber Is Null;


Groeten,

Peter
http://access.xps350.com

 

[John Spencer]

If you are doing this in design view, you can set up the join by dragging.

Drag from MainNumber to Mainnumber
and then
Drag from FollowoupNumber to FollowupNumber

You should end up with two join lines.
Double click on each join line and in the dialog box select All records from
table1 and matching from table2.

Add the fields you want to see
Add Table2.MainNumber and set its criteria to Is Null.

In the SQL view that would look like

SELECT Table1.MainNumber, Table1.FollowupNumber
FROM Table1 LEFT JOIN Table2
ON Table1.MainNumber = Table2.MainNumber
AND Table1.FollowUpNumber = Table2.FollowUpNumber
WHERE Table2.MainNumber Is NULL

In the query
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Peter]

Thanks to both XPS350 and John Spencer.

Thanks for answering so quickly, guys.
I'll check it out tomorrow when I'm all freshen'd up. It's been a long day
for me (I'm from Holland) and I did not expect to get an answer so fast...

Thnx again
Peter