Add If an item does not exist in another table?

Question

I have a table record source (contains tons of information), and I have a table containing 1200 records. I would like to add to this destination table (containing 1200 entries) any entries that do not currently exist. criteria for adding my elements:

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

The problem is that it returns the record I want back 1200 times, not once.

Example:

 Table A: Table B: Item Number Item Number 12345 45678 45678 

"12345" will only be added to table B once (and then never added again!) I searched for several solutions and I tried to use the unrivaled query wizard, but I don’t think this is really what I wanted (It is where the number is equals NULL). What do I need to do to get this view to look at the entire area of ​​the table, and not at the element by element (I assume why it is filled as many times as the existing records)? Which step am I leaving?

Answer 1

The general form of your request will look 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.

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