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:
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:
- Create a new query in design view
- Select the table from which you want to remove duplicate records and click Add; then Close
- Go to Query and select Delete Query
- Go to View and select SQL View
- 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. - Save and Run the Query.