Assign new ID from duplicate records

[Nils Pettersson]

Hi,

My table contains records with duplicate IDs.

ID Name
AAA John Doe
AAA Jane Doe
AAA Baby Doe
FFF Gorilla
FFF Giraffe

I am trying to find a way to make each ID unique for each name by appending
a number it.

ID Name
AAA01 John Doe
AAA02 Jane Doe
AAA03 Baby Doe
FFF01 Gorilla
FFF02 Giraffe

I hope this is clear. I simply want to add numbers to the ID starting with 1
or 01 and start with 1 again when it encounters a different ID.

Many thanks.
Nils

 

[Ofer Cohen]

First Back Up your data, then try this code

Function UpdateTable()
Dim MyDB As DAO.Database, MyRec As DAO.Recordset
Dim MyCount As Integer, OldValue As String
OldValue = ""
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("Select ID from TableName Order By ID")
While Not MyRec.EOF
If OldValue = MyRec!ID Then
MyCount = MyCount + 1
Else
MyCount = 1
End If
OldValue = MyRec!ID
MyRec.Edit
MyRec!ID = MyRec!ID & Format(MyCount, "00")

MyRec.Update
MyRec.MoveNext
Wend
End Function

 

[John Spencer]

If you can guarantee that the Name field is unique then you could try

UPDATE YourTable
SET ID = ID & DCount("ID","YourTable","ID=""" & [ID] & """ AND [Name] <="""
& [Name] &"""")

Alternative with a limited number of duplicated ids might be to repeat the
query below changing the 1 to 2, 3, 4, etc.
UPDATE YourTable
SET ID = [Id] & "1"
WHERE
(SELECT Count([Name])
FROM YourTable as YT
WHERE YT.ID = YourTable.ID and YT.Name <=YourTable.Name) = 1
AND YourTable.ID Not Like "*#"

<<< add the last criteria to only update records that do not end in a
number>>> hopefully keeping you from updating records that have already been
updated.

AS always, back up your data FIRST. There is no going back

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Nils Pettersson]

Thank you so much John! I've been trying so hard to get the right logic for
this to work in a query. It's been a thorn on my side for so long!

If you can guarantee that the Name field is unique then you could try

UPDATE YourTable
SET ID = ID & DCount("ID","YourTable","ID=""" & [ID] & """ AND [Name] <="""
& [Name] &"""")

Alternative with a limited number of duplicated ids might be to repeat the
query below changing the 1 to 2, 3, 4, etc.
UPDATE YourTable
SET ID = [Id] & "1"
WHERE
(SELECT Count([Name])
FROM YourTable as YT
WHERE YT.ID = YourTable.ID and YT.Name <=YourTable.Name) = 1
AND YourTable.ID Not Like "*#"

<<< add the last criteria to only update records that do not end in a
number>>> hopefully keeping you from updating records that have already been
updated.

AS always, back up your data FIRST. There is no going back

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

My table contains records with duplicate IDs.

ID Name
AAA John Doe
AAA Jane Doe
AAA Baby Doe
FFF Gorilla
FFF Giraffe

I am trying to find a way to make each ID unique for each name by
appending
a number it.

ID Name
AAA01 John Doe
AAA02 Jane Doe
AAA03 Baby Doe
FFF01 Gorilla
FFF02 Giraffe

I hope this is clear. I simply want to add numbers to the ID starting with
1
or 01 and start with 1 again when it encounters a different ID.

Many thanks.
Nils

 

[Nils Pettersson]

Thanks Ofer. This worked just as well. This information will prove very useful.