Multiple fields with NOT IN Subquery

[robert d via AccessMonster.com]

Can I use multiple fields for the Not In Subquery. For example:

WHERE FIELD1, FIELD2, FIELD3 NOT IN (SELECT FIELD4, FIELD5, FIELD6 FROM
TABLEA).

What I want is the combination of Field1, Field2, Field3 to not be in the
subquery. These three fields constitute the key.

Thanks.

 

[Duane Hookom]

One possible solution is
WHERE FIELD1 & FIELD2 & FIELD3 NOT IN (SELECT FIELD4 & FIELD5 & FIELD6 FROM
TABLEA)

 

[Marshall Barton]

Can I use multiple fields for the Not In Subquery. For example:

WHERE FIELD1, FIELD2, FIELD3 NOT IN (SELECT FIELD4, FIELD5, FIELD6 FROM
TABLEA).

What I want is the combination of Field1, Field2, Field3 to not be in the
subquery. These three fields constitute the key.

No, you can't do that.

You can simulate that by using something like:
WHERE FIELD1 & "~" & FIELD2 & "~" & FIELD3 NOT IN (SELECT
FIELD4 & "~" & FIELD5 & "~" & FIELD6 FROM TABLEA)

But this kind of thing usually amounts to a frustrated outer
join (the wizard's unmatched query):

FROM tableB As B LEFT JOIN tableA As A
ON B.FIELD1 = A.FIELD4
AND B.FIELD2 = A.FIELD5
AND B.FIELD3 = A.FIELD6
WHERE A.FIELD4 Is Null
AND A.FIELD5 Is Null
AND A.FIELD6 Is Null

 

[Tom Ellison]

Dear Robert:

You can do this via a different mechanism from NOT IN. Use NOT EXISTS with
a correlated subquery instead:


WHERE NOT EXISTS(SELECT *
FROM TableA A
WHERE A.Field4 = X.Field1
AND A.Field5 = X.Field2
AND A.Field6 = X.Field3)

In the above, replace X with the name or alias of the table from which
Field1, Field2, and Field3 are supplied.

If you have trouble doing this, please supply the SQL of what you used and
the nature of the error message received, or other difficulty experienced.

Tom Ellison