Solved

WHERE NOT EXISTS in Access

Posted on 2007-04-10
6
31,436 Views
Last Modified: 2012-06-27
Hello.

I am using MS Access 2000 and after repairing an old database and  importing tables from another database (that users copied... and were inserting information), I wrote a few queries to insert any missing records from table B to table A.  I also created a new field in the original tables and the imported tables called GroupID.  Tables from the imported tables have a GroupID of 2.  So when I update the records, i know which tables the records originally resided.

the problem is that this seemed to work just for one group.  I am using WHERE NOT EXISTS.  And if I understand correctly, based on the subquery, if the subquery is TRUE or FAlse, the outer statement is run (please tell me if I am wayyyyy off).

can someone tell me if I am on the right track?  I know that syntax for writing queries in Access is different:

Example to insert into tbl_checkouts, all missing records from tbl_checkouts1.

I ran a select query first to determine the records that should be inserted (but it seems that my select query is different than my insert's select statement):

Show_MissingRecords_tblCheckouts

SELECT [User ID], [Check out Date], GroupID
FROM tbl_checkouts
WHERE NOT EXISTS
    (SELECT * FROM tbl_checkouts1 WHERE tbl_checkouts1.[User ID] = tbl_checkouts.[User ID]);


Insert_missingRecords_checkouts

INSERT INTO tbl_checkouts ( [User ID], [Check out Date],GroupID)
SELECT [User ID], [Check out Date], GroupID
FROM tbl_checkouts
WHERE NOT EXISTS
    (SELECT * FROM tbl_checkouts1 WHERE tbl_checkouts1.[User ID] = tbl_checkouts.[User ID]);

the problem is that I am not sure if my exists statement is correct in access..

Thanks in advance
0
Comment
Question by:synergeticsoul
  • 4
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 18882551
WHERE tbl_checkouts.[User ID] NOT IN  (SELECT tbl_checkouts1.[User ID]  FROM tbl_checkouts1)
0
 

Author Comment

by:synergeticsoul
ID: 18882700
sorry, working on a new table now...
so, I want to grab the records that are in the second table (labeled, NAME+1) and see if these records exists in the main table...i want to check for missing data.  therefore, i need to do the following??

SELECT * FROM [tbl_checkout item] '[tbl_checkout item] is the original table
WHERE  [tbl_checkout item].[fk tape ID] NOT IN  (SELECT [tbl_checkout item1].[fk tape ID]  FROM [tbl_checkout item1])??
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 18882740
>so, I want to grab the records that are in the second table (labeled, NAME+1)
>and see if these records exists in the main table...

A better approach would be this way...

SELECT YourNAME+1Table.ID, YourNAMETable.ID
FROM YourNAME+1Table
LEFT JOIN YourNAMETable on YourNAME+1Table.ID = YourNAMETable.ID

All YourNAME+1Table records will be returned, and if the record is in the YourNAMETable table than that ID will be returned, otherwise that column will be NULL.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:synergeticsoul
ID: 18882889
ok...when I do the following query:

SELECT [tbl_checkout item1].ID, [tbl_checkout item].ID
FROM  [tbl_checkout item1]
LEFT JOIN  [tbl_checkout item] ON  [tbl_checkout item1].ID= [tbl_checkout item].ID

i just get the total records that are in [tbl checkout item1].  i just need to pull all the records from  [tbl_checkout item1] (and there are other tables like this) that don't exist in the original table:
 [tbl_checkout item].  I am then going to select those records and insert them into the original table:
 [tbl_checkout item].

am i better off first doing the join you mentioned above or something else???

SELECT * FROM [tbl_checkout item] '[tbl_checkout item]
WHERE  [tbl_checkout item].[fk tape ID]
NOT IN  (SELECT [tbl_checkout item1].[fk tape ID]  FROM [tbl_checkout item1])
0
 

Author Comment

by:synergeticsoul
ID: 18882998
Yes...this doesn't work:

SELECT ID, [fk tape ID], [date checked in], [fk checkout ID], [in Library]
FROM [tbl_checkout item]
WHERE [fk tape ID] NOT IN
 (SELECT  [tbl_checkout item1].[fk tape ID]  FROM [tbl_checkout item1])

it still returns records that exist in the main table.  open to suggestions...
0
 

Author Comment

by:synergeticsoul
ID: 18885023
I got it finally.  I used WHERE NOT EXISTS.

thanks so much  
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question