[Webinar] Streamline your web hosting managementRegister Today

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

Not IN

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])));


APPEND_NOTIN
0
pdvsa
Asked:
pdvsa
1 Solution
 
Kent DyerIT Security Analyst SeniorCommented:
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
0
 
pdvsaProject financeAuthor Commented:
Kent:  thanks for the response. I do get a syntax and it highlights the WHERE right below the INSERT.  what do you think now?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
jerryb30Commented:
INSERT INTO tblConsortium ( agtID )
SELECT tblConsortium.agtID
FROM tblAgreements_thisPrj LEFT JOIN tblConsortium ON tblAgreements_thisPrj.agtID = tblConsortium.agtID
WHERE (((tblConsortium.agtID) Is Null));
0
 
pdvsaProject financeAuthor Commented:
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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