MS Access Running Count With Duplicates

This post is a response to a video I have on Youtube:

MS Access Running Counter In Query

“After running your solution in different databases, I found out that it only works if there are no duplicate values within the Number field (in your tblRunningSum). Once there are duplicate values, the row numbering gets corrupted. Any idea how to fix this? I’m desperately trying to find a solution for numbering row in a query when there are duplicates values in my main field.”

I see the issue, the way to order the records with Dcount works when we don’t have any duplicates, but when there are duplicates, all the records containing the same value get the same order by value.

Here is the way I probably would deal with it according to the knowledge I have about the issue:

1. Add a field to “tblRunningSumDups” (just a copy of tblRunningSum)

…and then I would run the following code (new module, then press F5 to run the code) to add some numbers to put the records to order by.

Sub AddCounter()
    Dim rst As Recordset
    Dim intCounter As Integer
    
    Set rst = CurrentDb.OpenRecordset("SELECT OrderBy FROM tblRunningSumDups ORDER BY number", dbOpenDynaset)
    
    intCounter = 1
    
    
    Do Until rst.EOF
        rst.Edit
        rst.Fields("OrderBy") = intCounter
        rst.Update
        
        intCounter = intCounter + 1
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    
End Sub

Here is the result:

Please let me know if you have any questions.

By the way, if you got or are getting value from the VBA information, please click the "Donate" button to give me a small token of your appreciation, thanks!