Solved

WHERE NOT EXISTS in Access

Posted on 2007-04-10
6
30,795 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
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)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now