Making duplicates unique

  • Thread starter Thread starter GD
  • Start date Start date
G

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!!!!
 
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
 
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


John Spencer said:
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!!!!
Click to expand...
Click to expand...
 
Back
Top