How do I correctly use two Not Exists statements in a where clause using Access SQL VBA?

Question

I have 3 Tables: NotHeard,analyzed,analyzed2. In each of these tables I have two columns named UnitID and Address.

What I'm trying to do right now is to select all of the records for the columns UnitID and Address from NotHeard that don't appear in either analyzed or analyzed2. The SQL statement I created was as follows:

SELECT UnitID, Address  
INTO [NotHeardByEither] 
FROM [NotHeard] 
Where NOT EXISTS( Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID) 
or NOT EXISTS( Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID) 
Group BY UnitID, Address 

I thought this would work since I've used the single NOT EXISTS subquery line and it has worked just fine for me in the past. The above query however returns the same data that is in the NotHeard table whereas if I take out the or NOT EXISTS part it works correctly.

Any ideas as to what I'm doing wrong or how to do what I'm wanting to do?

Answer 1

It looks like you want left joins instead. I may be a little off on my Access syntax.

SELECT UnitID, Address  
INTO [NotHeardByEither] 
FROM [NotHeard] 
    LEFT JOIN [analyzed] ON [NotHeard].UnitID = [analyzed].UnitID
    LEFT JOIN [analyzed2] ON [NotHeard].UnitID = [analyzed2].UnitID
WHERE IsNull([analyzed].UnitID)
    AND IsNull([analyzed2].UnitID)
Group BY UnitID, Address 

Answer 2

Regarding you original query, try

NOT (
   EXISTS( 
    Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID) 
AND EXISTS( 
    Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID) 
    )

which would mean not in either. But that's equal to what you had originally (tested on sample data). Are you sure that you don't mean not in A neither in B? That would be

NOT (
   EXISTS( 
    Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID) 
OR EXISTS( 
    Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID) 
    )

Do realize that the EXISTS solution is using correlated subqueries which might perform worse then LEFT JOIN and NULLs, here's a sample.

SELECT NotHeard.UnitID, NotHeard.Address
FROM (NotHeard LEFT JOIN analyzed ON NotHeard.UnitID = analyzed.UnitID) 
     LEFT JOIN analyzed2 ON NotHeard.UnitID = analyzed2.UnitID
WHERE analyzed.UnitID Is Null OR analyzed2.UnitID Is Null
GROUP BY NotHeard.UnitID, NotHeard.Address;

Notice that I used OR in the condition as compared to Austin's solution, AND would give you neither in analyzed nor in analyzed2.