Client_Id_Num Last_Name First_Name SeqNum
00-1365 Smith Alfred 1
00-1365 Smith Alfred 2
00-1365 Smiths Alfred 1
00-1412 Jones Alan 1
00-1412 Jones Alan 2
00-1412 Jones Alan 3
00-1550 Doe Jeff 1
00-1550 Doe Mitzi 1
02-1036 (dtr) Terri 1
02-1036 Thomas Terry 1
02-1036 Pear Stacy 1
02-1036 (dtr) Stacy 1
02-3362 Lemon Alfredo 1
02-3362 Lemon Alfredo 2
02-3362 Lemon Guadalupe 1
04-1005 Cherry Omar 1
04-1005 Orange Marisol 1
04-1005 Orange Marisol 2
04-1126 Banana Deborah 1
04-1126 Banana Deborah 2
04-1126 Apple Tristan 1
04-1126 Apple Deborah 1
ClientID = "0"
FName = "ZZ"
LName = "ZZ"
Do While RS.EOF = False
If ClientID <> RS!Client_Id_Number Then
I = 1
With RS
ClientID = !Client_Id_Number
FName = !First_name
LName = !Last_name
.Edit
!SeqNum = I
.Update
End With
Else
If LName <> RS!Last_name Then
If FName <> RS!First_name Then
I = 1
With RS
ClientID = !Client_Id_Number
LName = !Last_name
FName = !First_name
.Edit
!SeqNum = I
.Update
End With
Else
I = I + 1
With RS
.Edit
!SeqNum = I
.Update
End With
End If
Else
I = 1
With RS
ClientID = !Client_Id_Number
LName = !Last_name
.Edit
!SeqNum = I
.Update
End With
End If
End If
RS.MoveNext
Loop
RS.Close
SQL = "SELECT Facility_Code, Client_Id_Number, Last_Name, First_Name, ContactType01, SeqNum " & _
"FROM ContactsTbl " & _
"ORDER BY Facility_Code, Client_Id_Number, Last_Name, First_Name, ContactType"
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)
SQL = "SELECT ContactsTbl.Client_Id_Number, ContactsTbl.Last_Name, ContactsTbl.First_Name, (select count(*) from ContactsTbl as x where x.Client_ID_Number =ContactsTbl.Client_ID_Number and x.Last_Name=ContactsTbl.Last_Name and x.First_Name=ContactsTbl.First_Name and x.ID_Num<=ContactsTbl.ID_Num) AS Seq, ContactsTbl.ID_Num, ContactsTbl.ContactType01 " & _
"FROM ContactsTbl " & _
"ORDER BY ContactsTbl.Client_Id_Number, ContactsTbl.Last_Name, ContactsTbl.First_Name, ContactsTbl.ContactType01, ContactsTbl.ID_Num;"
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)
If RS.EOF = False Then
RS.MoveFirst
Do While RS.EOF = False
SQL = "UPDATE ContactsTbl " & _
"SET Seq_Num = " & RS!Seq & " " & _
"WHERE ID_Num = " & RS!ID_Num
DoCmd.SetWarnings False
DoCmd.RunSQL SQL, False
DoCmd.SetWarnings True
RS.MoveNext
Loop
End If
RS.Close
I think your code should work, but it is crucial that you are sorting by Client_Id_Num, Last_Name, First_Name.
Not sure what your mission is, but wouldn't you be better off introducing a unique, artificial primary key?