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?.

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.