append a word to a field in a table

[Guest]

I would like to find all duplicates in a table. The name of the field is "new
SSN" it has numbers in it in front of those numbers I want to add the letters
DUP

The end result would have all duplicates and non-duplicates
and the duplicate would have the letters DUP in front of them.
Tahnks,
Harold

 

[John Spencer (MVP)]

First, I would suggest that you add another field. That field would be used to
record the fact that there are duplicate SSN in the table.

BETTER would be to use a query to calculate duplicate values.

One way (Probably slow) using a calculated field and the DCount function.

Field: HasDupe: IIF(DCount("*","YourTableName","[New SSN]=""" & [New SSN] &
"""") > 1,"Duplicate")


Faster way, would be to use a query to id all the duplicates and then link that
to another query.

SELECT [New SSN]
FROM YourTable
GROUP BY [New SSN]
HAVING Count(*) > 1

Save that as qDupes

SELECT YourTable.*,
IIF(qDupes.[New SSN] is Null, "", "Duplicate")
FROM YourTable LEFT JOIN qDupes
ON YourTable.[New SSN] = qDupes.[New SSN]

And if you really wanted to you could probably do all that in one query.