MS Access Running Count With String Column And Duplicates

In my previous post, https://vbahowto.com/ms-access-running-count-with-duplicates/, the DCount counter, works fine when you have a numeric value, and it works fine when you do the VBA counter when you do it on numeric column values, but what about counters on string columns with duplicates?

MS Access Running Count With String Column And Duplicates

Since an Access query doesn’t have an autofill handle like an Excel worksheet, we need to be a little creative.

In this example I am using data from the Northwind database’s 2 tables “Customers” and “Orders”.

I am combining just 2 fields into the query called “qryCustomersAndOrders”.

This simply shows a list of customers (duplicates) and their various order dates.

From here we want to add a counter to the data set.

However, our DCount counter, has no number to evaluate the previous record.

So we can add a counter to this result set by first sorting the query the way we want, and then append it to another “temporary” table, when we can add a counter and base the report on it.

Once we set the query just the way we want it, we need to run the following bit of VBA code:

Sub AddCounterToData()
    Dim strSQLAdd As String
    Dim rst As Recordset
    Dim intCounter As Integer
    
    '*************************************************************
    'INSERT THE QUERY RESULTS INTO A NEW TABLE (MakeTable Query)
    '*************************************************************
    strSQLAdd = "SELECT CompanyName, OrderDate, 0 AS [OrderBy] INTO tblNewCustomersAndOrders FROM qryCustomersAndOrders;"
    
    '*************************************************************
    'TURN OFF THE NORMAL QUERY WARNINGS
    '*************************************************************
    DoCmd.SetWarnings False
    
    '*************************************************************
    'RUN THE SQL COMMAND TO CREATE THE TABLE
    '*************************************************************
    DoCmd.RunSQL strSQLAdd
    
    '*************************************************************
    'TURN BACK ON THE NORMAL QUERY WARNINGS
    '*************************************************************
    DoCmd.SetWarnings True
    
    '*************************************************************
    'ADD A COUNTER TO THE NEW TABLE
    '*************************************************************
    Set rst = CurrentDb.OpenRecordset("SELECT OrderBy FROM tblNewCustomersAndOrders ORDER BY CompanyName,OrderDate", 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

After running this (F5 in the procedure), the new table “tblNewCustomersAndOrders” will be formed, and it will look like this:

Now you can base your report on this.

Let me know if you have any questions. You can download the database here:
ms access running count in query.accdb

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!