:confused: Duplicates

I have all my master data in my table which has a lot of duplicates (700 entries). I ran the find duplicate query offered by access, but exported the data to excel to delete all the duplicates.

My question is how do I import that updated excel list back into the duplicates query so that it updates my master data in my table?

Thanks

There are a couple of ways you can go about this.

If any of the data duplicated is/shouldbe unique then you can create a copy of your table using copy and paste, and copy the structure only. Make that field that should be unique indexed, no duplicates and run an append query from your old, duplicated table into the new one. This will throw up an error message that x number of records couldn't be imported, but will have de-deduped your data. When you're happy that everything is correct, rename your new table to the old (backup or delete your old table if you are happy it all worked) the main advantage to this is that you don't affect your existing data until you're happy that everything is ok.

If this isn't feasible on one column, it might be feasible to make your multiple columns where the duplication occurs a composite primary key instead and then run an insert query from the old table structure to the new. I don't think you can create a composite index across multiple columns in Access. You might need to remove the composite primary key afterwards depending on how your tables are designed/related.

If you really want to use the data in the excel sheet, and it contains an index field or other unique piece of data (a customer reference, serial number etc) then you can use your excel data to be the records in your table to be deleted.
1) BACK UP YOUR OLD TABLE
2) CHECK THAT IT IS DEFINITELY BACKED UP AND THE DATA IS GOOD
3) import the spreadsheet into a table.
4) Create a query:

DELETE *
FROM [Your table containing Duplicates]
WHERE [a unique field] IN
(SELECT [the unique field]
FROM [The table containing your excel data])


You should see on the "are you sure you want to run this" dialogue the same number of records that are contained in your excel data. If you're happy that the numbers look right, run the query, and then check the data.