Delete Multiple Instances of Email Address

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

Jennifer Sanders

We have an ecommerce site that accumulates user data in
Access. I would like to set up a query that deletes all
extra instances of the same email address. It should not
delete the entire user record--just the email address. It
should delete all instances of the email address except
the on associated with the highest User_ID.
 
Jennifer,

Please refer to my response to your earlier post, but in this case:
UPDATE YourTable SET User_ID = Null WHERE User_ID Not In(SELECT
Max([User_ID]) FROM YourTable GROUP BY EmailAddress)

- Steve Schapel, Microsoft Access MVP
 
O beleaguered query! I tried the duplicate data wizard in
Access and it gives me errors and shuts down (says there
was a software installation problem but there's no way I
can reinstall Access since the site runs on it).

Tried your SQL, but I get an error 'User_ID is not
updateable'--probably because it's the primary key for
that table.
-----Original Message-----
Jennifer,

Please refer to my response to your earlier post, but in this case:
UPDATE YourTable SET User_ID = Null WHERE User_ID Not In (SELECT
Max([User_ID]) FROM YourTable GROUP BY EmailAddress)

- Steve Schapel, Microsoft Access MVP


We have an ecommerce site that accumulates user data in
Access. I would like to set up a query that deletes all
extra instances of the same email address. It should not
delete the entire user record--just the email address. It
should delete all instances of the email address except
the on associated with the highest User_ID.
Click to expand...

.
Click to expand...
 
Jennifer,

Oops! Sorry, there was an error in the hasty SQL I gave in my
previous post. It wasn't the User_ID that you wanted to set to null,
was it? Please adjust accordingly.

- Steve Schapel, Microsoft Access MVP
 
Back
Top