count problem on recordset 2

[WOTRAC]

Hi

I have an Access table containing over 10,000 Customer records.
The SURNAME field contains hundreds of the same Surname
eg Jones, Smith etc.

I want to suffix the Surname with a number and increment this for each field where the surname is the same, only resetting when the Surname changes


JONES0001
JONES0002
SMITH0001

Can someone point me in the right direction.

N.B I already have the master recordset containing all the Surnames

 

[TheAceMan1]

How are ya WOTRAC . . . . .

Try this:

The following code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

In a module in the modules window, copy/paste the following code ([purple]you![/purple] substitute proper names in [purple]purple[/purple]):

[blue]Public Sub EnumSurNames()
   Dim db As DAO.Database, SQL As String
   Dim rstDup As DAO.Recordset, rstEnum As DAO.Recordset
   Dim SN As String, TN As String, PK As String
   Dim n As Long, Suffix As String
   
   Set db = CurrentDb()
   SN = "[purple][b]SurName[/b][/purple]" [green]'surname FieldName[/green]
   TN = "[purple][b]tblSurNames[/b][/purple]" [green]'surname TableName[/green]
   PK = "[purple][b]SurNameID[/b][/purple]" [green]'PrimaryKey FieldName[/green]
   
   [green]'Setup Duplicates Recordset.[/green]
[green]   'The SQL returns a single record for each duplicate group.[/green]
   SQL = "SELECT " & SN & " " & _
         "FROM " & TN & " " & _
         "GROUP BY " & SN & " " & _
         "HAVING Count(" & SN & ")>1;"
   Set rstDup = db.OpenRecordset(SQL, dbOpenSnapshot)
   
   If Not rstDup.BOF Then [green]'Record/s exist.[/green]
      Do
         [green]'Setup recordset for duplicate group editing.[/green]
         SQL = "SELECT " & PK & ", " & SN & " " & _
               "FROM " & TN & " " & _
               "WHERE " & SN & " = '" & [b]rstDup(SN)[/b] & "';"
         Set rstEnum = db.OpenRecordset(SQL, dbOpenDynaset)
         n = 1
         
         Do
            [green]'Setup Numeric Suffix[/green]
            Suffix = LTrim(Str(n))
            If Len(Suffix) < 4 Then
               Suffix = String(4 - Len(Suffix), "0") & Suffix
            End If
         
            [green]'Append Suffix to surName.[/green]
            rstEnum.Edit
            rstEnum(SN) = rstEnum(SN) & Suffix
            rstEnum.Update
            
            [green]'Setup next duplicate in group.[/green]
            n = n + 1
            rstEnum.MoveNext
         Loop Until rstEnum.EOF
         
         [green]'Setup next duplicate group.[/green]
         rstDup.MoveNext
      Loop Until rstDup.EOF
   End If
   
End Sub[/blue]

To call the routine from VBA:

[blue]Call EnumSurNames[/blue]

You can call the routine from just about anywhere you like.

[purple]Thats it! . . . . give it a whirl and let me know . . . .[/purple]

See Ya! . . . . . .

 

[cmmrfrds]

A self join query will work if the table has an unique id.

Select
min(A.surname) + count(*)
From yourtable A
Inner Join yourtable B
where A.id <= B.id
Group by A.id


This requires a group by (i.e. aggregate) but since the ID is unique using Min(surname) will give the surname on the current row and the count will increment.

 

[cmmrfrds]

Whoops, will need to add check on surname to reset the count.

Select
min(A.surname) + count(*)
From yourtable A
Inner Join yourtable B
where A.id <= B.id and A.surname = B.surname
Group by A.id