Query to exclude items from another table

[BrockWade]

I am trying to write a query that will update a table (tmpChangeOrders) but exclude records that have a value in the table to be updated (tmpChangeOrders.ConcatenateBank) that match another table's values (tmpJournalEntryChangeOrders.Concatenate)... the following query does not work.... any ideas?.... Thanks!!

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

[June7]

You want to select records from tmpBank where Store&Amount value not in tmpChangeOrders? Try including tmpChangeOrders in the SELECT DISTINCT statement joined to tmpBank.

[BrockWade]

I'm sorta confused... won't that mismatch the number of values to insert by adding tmpChangeOrders to the SELECT DISTINCT statement?

[June7]

I don't know why but then I don't know anything about your data structure. Regardless, the filter criteria needs to be in the SELECT DISTINCT subquery and since you are using a field from tmpChangeOrders to filter on, that's where the table needs to be.

Because a WHERE clause in an INSERT sql makes no sense. Need to filter the SELECT recordset.

[BrockWade]

I have a table tmpChangeOrders with four fields: Store, Amount, ConcatenateBank, TransactionDate.

I need to fill this table with the contents of the table tmpBank with its fields: Store, Amount, ConcatenateBank, TransactionDate.

But I have a third tmpChangeOrders with three fields: Store, Amount, Concatenate.

I need to keep any records in tmpBank with a matching Concatenate/ConcatenateBank value from filling into the tmpChangeOrders file.

I'm really in the dark about how to do this. My original query below was doing its job fine until the new challenge came up:

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

[June7]

Build the SELECT query as I described and save it.

Build INSERT query that references the first query in SELECT subquery.

If still need help, provide db for analysis. Follow instructions at bottom of my post.