How to De-dupe Email Addresses in MS Access

While Campaign Enterprise does contain a de-dupe (de-duplication) email address feature in the Datasource Tab when you edit a campaign, it’s best to take care of duplicate entries permanently on the source database table. Here is how to do that with Microsoft Access.

You can use this query to delete records with duplicate email addresses, however, the Access table must have an autonumber type of field as the unique identifier. Here are the steps:

1. Create a new query in design view
2. Select the table from which you want to remove duplicate records and click Add; then Close
3. Go to Query and select Delete Query
4. Go to View and select SQL View
5. Use the following SQL statement:
   
   DELETE *
   FROM Table1
   WHERE (ID) NOT IN (SELECT First([Table1].ID) AS ID FROM [Table1] GROUP BY [Table1].Email);
   
   Table1 is the table with the duplicate email addresses in it, ID is your unique id field and Email is your email address field. You will need to rename this information in the SQL Statement to match the information from the table you are using. This example will keep the first instance of the email address in your table and remove any instances after that. Backing up your original table before you run this query would be a good idea.
6. Save and Run the Query.

It is important to keep your email list free of duplicate entries so that you don't accidentally send out multiple email messages to the same address. If you collect subscribers via web form, please consider disallowing duplicate entries in the first place. If duplicates do occur, re-run this query every so often, and as a final fail-safe, check the de-dupe during send feature on the Datasource Tab of Campaign Enterprise.