Removing Duplicates in an Append Query

  • Thread starter Thread starter J
  • Start date Start date
J

J

I have an append query that I want to take all new information from one
table and drop it into another table that has all the information from
the last time that this append query was run. The problem that I'm
getting is that if I click the button more than once, all the records
get appended to the table, even the old ones that are already in the
table. If someone could help me out it would be appreciated.
 
First you must decide what exactly constitutes a duplicate. Then come the
hard choices.

Possible the best method is to create a unique index (maybe even the primary
key) of those fields in the table being appended. Of course you will need to
deal with any current dupes in the table before Access will create a No
Duplicates index. When running the append query Access will give you an error
saying how many records are not going to be appended but let the unique
records in. You can even SetWarnings Off to stop the error message from
bothering you.

The other way would be to use a subquery to select only the new records.
Something with a NOT IN or NOT EXISTS clause in the query.

Again you MUST decide what constitutes a duplicate first.
 
Back
Top