Making duplicates unique

[GD]

If I have a field that contains duplicate numbers, how would I go about
adding letters to make them unique. In other words, if I have three 1s in
the field, I need to change the first 1 to 1A, the second 1 to 1B and the
third 1 to 1C.

They will be in succession recordwise, always sorted by an autonumber field
in the table, if that makes any difference.

Is there an easy way to accomplish this?

THANKS!!!!

 

[John Spencer]

You might try a query that looks like the following

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [YourTable]
SET [YourField] = [YourField] & Asc(65 + DCount("*","YourTable","YourField="""
& [YourField] & """ AND AutonumberField < " & [AutoNumberField]))
WHERE [YourField] in
(SELECT [YourField]
FROM [YourTable]
GROUP BY [YourField]
Having Count([YourField]) > 1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[GD]

Thanks, John. But not quite there. It added 54 to the end of all the
duplicate numbers, instead of A, B, etc.

Any other thoughts?
--
GD

You might try a query that looks like the following

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [YourTable]
SET [YourField] = [YourField] & Asc(65 + DCount("*","YourTable","YourField="""
& [YourField] & """ AND AutonumberField < " & [AutoNumberField]))
WHERE [YourField] in
(SELECT [YourField]
FROM [YourTable]
GROUP BY [YourField]
Having Count([YourField]) > 1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

If I have a field that contains duplicate numbers, how would I go about
adding letters to make them unique. In other words, if I have three 1s in
the field, I need to change the first 1 to 1A, the second 1 to 1B and the
third 1 to 1C.

They will be in succession recordwise, always sorted by an autonumber field
in the table, if that makes any difference.

Is there an easy way to accomplish this?

THANKS!!!!