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.