troubleshooting Question

Query - Not In

Avatar of pdvsa
pdvsaFlag for United States of America asked on
Microsoft Access
13 Comments1 Solution247 ViewsLast Modified:
Experts, I have a query that works OK but need to add extra criteria to it.  

In addition to the current criteria I need to remove any occurance where import-CSM2.[Reference Number] is like tblLetterOfCRedit.[BankRef]  

here is the sql:  

INSERT INTO tblLetterOfCredit ( ProjectID, ExpiredYN, LCNo )
SELECT [import-CSM2].ProjID, IIf([Import-CSM2]![Actual Status]="GEC",Yes,No) AS ExpiredYN, [import-CSM2].[Reference Number]
FROM [import-CSM2] LEFT JOIN tblLetterOfCredit ON [import-CSM2].[Reference Number] = tblLetterOfCredit.LCNo
WHERE ((([import-CSM2].ProjID) Is Null) AND ((IIf([Import-CSM2]![Actual Status]="GEC",Yes,No))=0) AND ((tblLetterOfCredit.LCNo) Is Null));

I was thinking that the below might somehow be a solution but I am not all too familiar with Not In or even if the solution calls for "Not In".  I tried it (in the criteria line) but it returned 0 records and it should only remove the one circled below in the pic.

Not In (SELECT [import-CSM2].[Reference Number]
 FROM [import-CSM2] INNER JOIN tblLetterOfCredit ON [import-CSM2].[Reference Number] not like "*"&[tblLetterOfCredit].[BankRef]&"*")

thank you

Here is an pic of the output.  I need to remove the one that is circled.   This record is where  import-CSM2.[Reference Number] is like tblLetterOfCRedit.[BankRef].

 Output
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 13 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros