[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 35738
  • Last Modified:

WHERE NOT EXISTS in Access

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
synergeticsoul
Asked:
synergeticsoul
  • 4
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
WHERE tbl_checkouts.[User ID] NOT IN  (SELECT tbl_checkouts1.[User ID]  FROM tbl_checkouts1)
0
 
synergeticsoulAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
synergeticsoulAuthor Commented:
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
 
synergeticsoulAuthor Commented:
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
 
synergeticsoulAuthor Commented:
I got it finally.  I used WHERE NOT EXISTS.

thanks so much  
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now