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

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

ASKER CERTIFIED SOLUTION

jerryb30

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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