Append If the item does not exist in another table?

Question

I have a table record source (contains tons of info), and I have a table that contains 1200 records. I would like to append into this destination table (containing 1200 records) any records that currently do not exist. the criteria for my items to be appended is:

"Not In ([TABLE - To Work].[Item Number])"

Problem is, it is returning the record I want, back 1200 times, instead of once.

For Example:

Table A:                    Table B:

Item Number                 Item Number
12345                       45678
45678

"12345" would append into table B only once (and then never append again!) I looked for a few solutions, and I tried using the unmatched query wizard, but I do not think it was really what I Wanted (It generated where the number is null). What do I need to do to make this sort of look at entire scope of the table and not item by item (I assume thats why it is populating the same number of times as existing records)? What step am I leaving out?.

Wera

Answer 1

The general form of your query will be something like

INSERT INTO [Table B] ( [Item Number] )
SELECT [Table A].[Item Number]
FROM [Table A]
WHERE [Table A].[Item Number] NOT IN (SELECT [Item Number] FROM [Table B]);

Note that [Table B] is not in the FROM clause of the main query, it is only in the FROM clause of the NOT IN subquery.

Agrimony

Answer 2

This worked for me:

INSERT INTO [Table B] ( [Item Number] )
SELECT DISTINCT [Table A].[Item Number]
FROM [Table A] LEFT JOIN [Table B] ON [Table A].[Item Number] = [Table B].[Item Number]
WHERE ((([Table B].[Item Number]) Is Null));

Heartbreaker

welcome to SO, shannon, an answer has already been given... and it was accepted too. – Lima

@MuhammadFaisalHyder, I do not see any issues here by submitting another answer as long as it is not carbon copy or heavily plagiarised version of any answer given elsewhere. This is a possible variant with different operator specifications, I think, which may help someone else, like myself. – Dine