By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,916 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,916 IT Pros & Developers. It's quick & easy.

Append Query Without Duplicate Rows

P: 2
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.
Aug 3 '10 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 107
That looks like it should work.

An alternate way that does not require a subquery is:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tableB
  2. SELECT tableA.*
  3. FROM tableA LEFT JOIN tableB ON tableA.ID = tableB.ID
  4. 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.
Aug 3 '10 #2

P: 2
Yes that worked perfectly. Thank You!
Aug 3 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.