Assign new ID from duplicate records

  • Thread starter Nils Pettersson
  • Start date
N

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
 
O

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
 
J

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
..
 
N

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!

John Spencer said:
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 said:
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
 
N

Nils Pettersson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top