Add Letter to Duplicate to Create Unique Entry

[Guest]

Hi all! I have a need for creating unique instances of duplicated entries in
a new field. An example of my data and what I would like is as follows:

What I have What I need
123 123A
123 123B
123 123C
789 789A
789 789B

Does anyone have an idea of how this can be accomplished? There could be as
many as four duplications of any number, so I would need to assign letters
from A to D. I really appreciate any help or input that can be provided.

Thanks,
Greg

 

[Guest]

Try a function like:

Function AddLetterToNumber()
Dim MyDb As DAO.Database, MyRec As DAO.Recordset, I As Integer, LastValue As
Long
Set MyDb = CurrentDb
' Open the table with the right order of the number
Set MyRec = MyDb.OpenRecordset("SELECT TableName.* FROM TableName ORDER BY
TableName.FieldName")
While Not MyRec.EOF
MyRec.Edit
'The chr with I will assign the next number
If LastValue <> MyRec!FieldName Then
I = 65
MyRec!FieldName2 = Chr(I) & MyRec!FieldName
LastValue = MyRec!FieldName
Else
I = I + 1
MyRec!FieldName2 = Chr(I) & MyRec!FieldName
End If
MyRec.Update
MyRec.MoveNext
Wend
End Function

 

[Guest]

Thank you so much for your response. This function has helped me immensely.

Sincerely,
Greg