Updating Field where there Dublicates

[greenpm959]

Is there anyway to update only the dublicates in a field. I have email address that are dublicates and want to add the last 4 digits of the SSN to dublicate only. I writing this for Access data base.
I tried:
UPDATE Reps
SET Email = FirstName + LastName + Mid(SSN,8,4) +'.'+ '@MyCollege.edu'
WHERE Email = Count(*)>1

I got a error that I couldn't use the WHERE clause with the Count. Tried CAST in front of the count. Any clues?

 

[lespaul]

Is there something unique about each record that contains the duplicate email addresses, like RepID? You're going to need that for Access to determine which one of the records to update. You may try the FIRST function instead, but that will only update one of the records, and if you have 6 with the same email address, that won't help either.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design