Append Query Without Duplicate Rows

[David Shears]

I am trying to use an append query to add data to another table i have. I have tableA and tableB. tableA has 1000 rows while tableB has 800 rows. i would like to add the other 200 rows in tableA to tableB without adding the 800 identical rows. I have an append query but there is an error that is bothering me.

INSERT INTO tableB
SELECT *
FROM tableA
WHERE NOT EXISTS (
SELECT *
FROM tableB
WHERE tableB.ID=tableA.ID)

This generates the error "The Microsoft Office Access database engine does not recognize 'tableA.ID' as a valid field name or expression". I cant seem to figure out how to resolve this error as tableA.ID is a valid field name. I can run a select query that pulls tableA.ID from tableA, but when i try to subquery it this error occurs.

[Steven Kogan]

That looks like it should work.

An alternate way that does not require a subquery is:

INSERT INTO tableB
SELECT tableA.*
FROM tableA LEFT JOIN tableB ON tableA.ID = tableB.ID
WHERE (((tableB.ID) Is Null));

You'll be able use Design View with this query, which might be more intuitive.

Let me know if this works for you.

[David Shears]

Yes that worked perfectly. Thank You!