Problems with query

[Duster]

Hi all,

I am facing the following problem:


Two tables:

Table1
Name Code
Hans A1
Hans A5
Hans A7
Bert A1
Ernie A2
Karel A3


Table2
Name Code
Hans A1
Hans A5
Bert A2
Karel A3

I would like to create a query that shows all records from Table1 that do
not occur in Table2.

Query result should be:

Name code
Hans A7
Bert A1
Ernie A2

How do I accomplish this?

Many thanks in advance

 

[John Spencer]

SQL for the query would look like
SELECT Table1.*
FROM Table1 LEFT JOIN Table2
ON Table1.Name = Table2.Name AND
Table1.Code = Table2.Code
WHERE Table2.Name is Null

There is a query wizard for doing this, but it only handles matching on one
field. You could use it to build the query on just the Name field and then
modify it to have the second join.

If you can only use the query grid to build queries, post back to get more
detailed instructions.

 

[Duster]

Perfect. Thanks John!

SQL for the query would look like
SELECT Table1.*
FROM Table1 LEFT JOIN Table2
ON Table1.Name = Table2.Name AND
Table1.Code = Table2.Code
WHERE Table2.Name is Null

There is a query wizard for doing this, but it only handles matching on
one field. You could use it to build the query on just the Name field and
then modify it to have the second join.

If you can only use the query grid to build queries, post back to get more
detailed instructions.