Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Not IN

Posted on 2013-01-01

Microsoft Access

1 solution

Medium Priority

274 Views

Last Modified: 2013-01-01

Experts, I have the below APPEND query.
I am trying to APPEND to tblConsortium where tblAgreements_thisPrj.AgtID is NOT IN tblConsortium.AgtID

There are records that should be APPENDED but when I run it there are 0 records to be APPENDED.

What do you think is missing?
thanks.

INSERT INTO tblConsortium ( AgtID )
SELECT tblAgreements_thisPrj.AgtID
FROM tblAgreements_thisPrj
WHERE (((tblAgreements_thisPrj.AgtID) Not In (SELECT [tblConsortium].[AgtID] FROM [tblConsortium])));

Comment

Question by:pdvsa

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 17

Expert Comment

by:Kent Dyer

ID: 387352142013-01-01

I think this is what you are looking for:

INSERT INTO tblConsortium ( AgtID )
WHERE
(SELECT tblAgreements_thisPrj.AgtID
FROM tblAgreements_thisPrj
WHERE ((
(tblAgreements_thisPrj.AgtID)
 Not In (SELECT [tblConsortium].[AgtID] FROM [tblConsortium])
))
);

Open in new window

HTH,

Kent

Author Comment

by:pdvsa

ID: 387352232013-01-01

Kent: thanks for the response. I do get a syntax and it highlights the WHERE right below the INSERT. what do you think now?

LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)

ID: 387352592013-01-01

How are you executing this Append query? Have you turned off SetWarnings ? Because if so, it may be masking a error that is occurring. Comment out any DoCmd.SetWarnings False and see if an error is occurring.

mx

LVL 26

Accepted Solution

by:jerryb30

jerryb30 earned 2000 total points

ID: 387352652013-01-01

INSERT INTO tblConsortium ( agtID )
SELECT tblConsortium.agtID
FROM tblAgreements_thisPrj LEFT JOIN tblConsortium ON tblAgreements_thisPrj.agtID = tblConsortium.agtID
WHERE (((tblConsortium.agtID) Is Null));

Author Closing Comment

by:pdvsa

ID: 387352802013-01-01

Jerry: that was it. I remember that Is Null trick now. I guess that method is better than NOT IN. I will write that down.

thank you

Featured Post

Question has a verified solution.

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

William Moulton Marston’s DISC Matrix applied to Email Etiquette

Article by: Dermot

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…

Dynamic Printer Selection for Access

Article by: Helen

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …

MS Access – Adding “Page ‘x’ of ‘y’” Over a Group in a Report

Video by: Jim

Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

Microsoft Access

MS Access – Repeating Sub-Report Headings on Each Page

Video by: Jim

In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…