Add only new records using an append qry

[Jon M.]

Afternoon everybody,
I am trying to run an append query to add records from one table not found
in another table. I can't seem to get it to work. I have a table called
ReInsp, and a table called NonPCC, both have a field called NonPCC. I want
table ReInsp to update table NonPCC. I can only get the query to run so that
ReInsp will copy all of it's records into NonPCC even if those records
already exist in NonPCC creating duplicates. The SQL that works for that is:

INSERT INTO NonPCC ( NonPCC )
SELECT ReInsp.NonPCC
FROM ReInsp;

I tried this to weed out the duplicates but I get when I run the query it
updates 0 rows:

INSERT INTO NonPCC ( NonPCC )
SELECT ReInsp.NonPCC
FROM NonPCC RIGHT JOIN ReInsp ON NonPCC.NonPCC = ReInsp.NonPCC
WHERE ((([NonPCC].[NonPCC])<>[ReInsp].[NonPCC]));

As the tree said to the lumber jack, I'm stumped. I'd appreciate any
suggestions.

 

[Beetle]

Make a backup, then try this;

INSERT INTO NonPCC (NonPCC) SELECT NonPCC FROM ReInsp WHERE
ReInsp.NonPcc NOT IN (SELECT NonPCC FROM NonPCC);

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

INSERT INTO NonPCC (NonPCC)
SELECT ReInsp.NonPCC
FROM ReInsp LEFT JOIN NonPCC
ON ReInsp.NonPCC = NonPcc.NonPCC
WHERE NonPCC.NonPCC is Null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County