asked on

Append if Not IN.

Experts,

I need to append to tblProjects if [Trade#1] is NOT IN [Trade No].
I am not sure if my query is correct.
If it is correct, when I execute it it says I can not add records due to type conversion failure and key violations.

this is my query:

INSERT INTO tblProjects ( [date], Trade_No_2, Trade_No, Buy_CP, Quantity_BBLS, Batch, Origin_Deal, Sale_CP )
SELECT XL.Date, XL.[Trade #1], I2A.Trade_No, XL.[Buy CP], XL.[Quantity BBLS], XL.Batch, XL.[Origin / Deal], XL.[Sale CP]
FROM XLImportToAccess AS XL LEFT JOIN tblProjects AS I2A ON XL.[Trade #1] = I2A.Trade_No
WHERE (((XL.Date) Is Not Null) AND ((I2A.Trade_No) Is Null));

Open in new window

I have attached the db. There are only 2 tables and 1 query. If you could kindly take a peek at it and let me know I would appreciate.
EE_NotIn.accdb

aikimark

Change the index on the tblProjects.[Trade No] column to be unique. You can greatly simplify your append, removing any NOT IN check.

pdvsa

ASKER

Hi Mark, unfortunately the data is not going to allow me to do that. There are duplicates on that field. The record is not a duplicate but there are duplicates on that particular field.

What do you suggest now?

aikimark

Given the two tables in your sample database, which rows SHOULD be appended and why? Conversely, why shouldn't the non-appended rows be appended?