Delete Query for Multiple Email Instances

  • Thread starter Thread starter Jennifer Sanders
  • Start date Start date
J

Jennifer Sanders

We run Access as a db for an e-com site that collects
email adddresses and other user info.

On a weekly basis, we send email to our entire list of
subscribers. However, sometimes a customer has registered
multiple times with the same email address and recieves
more than one email at a particular address (annoying).

Does anyone know a query that will delete multiple
instances of the same email address? Ideally, I would
like to delete every instance of the email address except
for the one associated with the highest User_ID (the most
recent).
 
Jennifer,

Access has a Find Duplicates Query Wizard (you will find it by
clicking New from the Queries tab in the Database Window), and this
will guide you to locating the duplicate email addresses.

However, in this instance, an alternative approach might be to simply
use a Totals Query, Group By EmailAddress, Max([User_ID]), and use
this as the basis of your mailouts. Or, if you really want to delete
the duplicates, make a Delete query based on the master table, the SQL
of which will look a bit like this...
DELETE * FROM YourTable WHERE User_ID Not In(SELECT Max([User_ID])
FROM YourTable GROUP BY EmailAddress)

- Steve Schapel, Microsoft Access MVP
 
Back
Top