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

Posted by Bryan on Stack Overflow See other posts from Stack Overflow or by Bryan
Published on 2010-04-01T21:04:48Z Indexed on 2010/04/01 23:43 UTC
Read the original article Hit count: 256

Filed under:

sql

|

ms-access-2007

|

not-exists

|

vba

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?

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about ms-access-2007

remove autoexec macro from MS Access 2007

as seen on Stack Overflow - Search for 'Stack Overflow'
I have created an autoexec macro in MS Access 2007 and cannot find it! I know its there because it runs, but I cannot find it to delete or modify. I can see other macros that I have created just not the autoexec one. Can someone please point me in the right direction to find where it is located… >>> More
How to connect to MS Access 2007, from Java on a mac

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm looking for a way to connect to a MS Access 2007 database from Java. I don't believe the ODBC way is available to me as I'm writing on a mac and will be pushing this product to Linux. The HXTT drivers also do not work with 2007. Is there a way, a tutorial, an example, etc that accomplishes this… >>> More
Can't change data type on MS Access 2007

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi All, I have a huge database (800MB) which consists of a field called 'Date Last Modified' at the moment this field is entered as a text data type but need to change it to a Date/Time field to carry out some queries. I have another exact same database but with only 35MB of data inside it and when… >>> More
Help with Ms Access 2007 Combo boxes

as seen on Super User - Search for 'Super User'
What's the most efficient way to "chain" combo/boxes in an Access 2007 form, so that the result of the first affected the contents of the second? I already know how to associate a combo box on a form with a query. Here's a example of my scenario: cmbCarMake Behavior: User starts typing, and list… >>> More
MS-Access 2007 Time Online Report

as seen on Stack Overflow - Search for 'Stack Overflow'
I have the following data in my database: MemberID | DateTime ------------------------------------- 1 | 31/03/2010 3:45:49 PM 2 | 31/03/2010 3:55:29 PM 1 | 31/03/2010 4:45:49 PM Every time a user is authenticated or un-authenticated this log appears in the database… >>> More