Avatar of pdvsa
pdvsa
Flag for United States of America asked on

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
Microsoft Access

Avatar of undefined
Last Comment
pdvsa

8/22/2022 - Mon
Kent Dyer

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
pdvsa

ASKER
Kent:  thanks for the response. I do get a syntax and it highlights the WHERE right below the INSERT.  what do you think now?
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
jerryb30

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
pdvsa

ASKER
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