"NOT IN" Query confusing me

[BennyLinton]

I have a successfully working INSERT query called "tmpChangeOrders" that I am wanting to filter out some records before the insert that also exist in another table called "tmpJournalEntryChangeOrders". the below is not working and was wondering if someone might be so kind as to point me in the right direction?:

INSERT INTO tmpChangeOrders ( Store, Amount, ConcatenateBank, TransactionDate )
SELECT DISTINCT tmpBank.Store, tmpBank.Amount, [tmpBank].Store & " " & [tmpBank].Amount AS ConcatenateBank, Format([tmpBank.TransactionDate],"mm/dd/yy") AS TransactionDate
FROM tmpBankDebits LEFT JOIN tmpBank ON tmpBankDebits.[ConcatenateBankDebits] = tmpBank.ConcatenateBank
WHERE tmpChangeOrders.ConcatenateBank NOT IN (SELECT tmpJournalEntryChangeOrders.Concatenate);

 

[WayneRyan]

Benny,

You forgot the FRom

(SELECT tmpJournalEntryChangeOrders.Concatenate From tmpJournalEntryChangeOrders);

Wayne

 

[BennyLinton]

I added the FROM and now it is saying there's a syntax error in this part:

"SELECT FROM tmpJournalEntryChangeOrders.Concatenate)"

 

[pr2-eugin]

SELECT <<WHAT>> FROM theTable? Best if you could show the entire Query.

 

[BennyLinton]

My bad.... I did not see the suggested change in its completeness:

INSERT INTO tmpChangeOrders ( Store, Amount, ConcatenateBank, TransactionDate )
SELECT DISTINCT tmpBank.Store, tmpBank.Amount, [tmpBank].Store & " " & [tmpBank].Amount AS ConcatenateBank, Format([tmpBank.TransactionDate],"mm/dd/yy") AS TransactionDate
FROM tmpBankDebits LEFT JOIN tmpBank ON tmpBankDebits.[ConcatenateBankDebits] = tmpBank.ConcatenateBank
WHERE tmpChangeOrders.ConcatenateBank NOT IN (SELECT tmpJournalEntryChangeOrders.Concatenate From tmpJournalEntryChangeOrders);

 

[pr2-eugin]

Why are you storing Concatenated values/Calculated values in the tmpChangeOrders table? Should the WHERE not be tmpBank.ComcatenateBank or tmpBankDebits.ConcatenateBankDebits, instead of tmpChangeOrders.ConcatenateBank?

 

[BennyLinton]

You are quite correct, good catch!... Thanks!

 

[BennyLinton]

Maybe (probably) I'm seeing this wrong but if I take the "WHERE tmpBankDebits.ConcatenateBankDebits NOT IN (SELECT tmpJournalEntryChangeOrders.Concatenate From tmpJournalEntryChangeOrders)" off my INSERT query it inserts 657 records... leaving the WHERE in inserts 0 records... puzzling since I know that in my table "tmpJournalEntryChangeOrders.Concatenate" there are two records that I'm trying to keep from inserting so I only really want to insert 655 records. And indeed the two records are in "tmpBankDebits.ConcatenateBankDebits"... ideas?

INSERT INTO tmpChangeOrders ( Store, Amount, ConcatenateBank, TransactionDate )
SELECT DISTINCT tmpBank.Store, tmpBank.Amount, [tmpBank].Store & " " & [tmpBank].Amount AS ConcatenateBank, Format([tmpBank.TransactionDate],"mm/dd/yy") AS TransactionDate
FROM tmpBankDebits LEFT JOIN tmpBank ON tmpBankDebits.[ConcatenateBankDebits] = tmpBank.ConcatenateBank
WHERE tmpBankDebits.ConcatenateBankDebits NOT IN (SELECT tmpJournalEntryChangeOrders.Concatenate From tmpJournalEntryChangeOrders);

 

[namliam]

Either you do the left join or do the NOT IN, doing both dont make sence at all....

SELECT DISTINCT tmpBank.Store, tmpBank.Amount, [tmpBank].Store & " " & [tmpBank].Amount AS ConcatenateBank, Format([tmpBank.TransactionDate],"mm/dd/yy") AS TransactionDate
FROM tmpBankDebits 
WHERE tmpBankDebits.ConcatenateBankDebits NOT IN (SELECT tmpJournalEntryChangeOrders.Concatenate From tmpJournalEntryChangeOrders);

or

SELECT DISTINCT tmpBank.Store, tmpBank.Amount, [tmpBank].Store & " " & [tmpBank].Amount AS ConcatenateBank, Format([tmpBank.TransactionDate],"mm/dd/yy") AS TransactionDate
FROM tmpBankDebits LEFT JOIN tmpBank ON tmpBankDebits.[ConcatenateBankDebits] = tmpBank.ConcatenateBank
WHERE tmpBank.ConcatenateBank is null

If your two records are not being excluded by either of these queries simply means that the ConcatenateBankDebits is not the same as ConcatenateBank