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
How to Fix Run Time Error 1004 in Excel
If you work with Microsoft Excel frequently, chances ar ling for a solution. Fortunately, this error is well-documented, and there are several ways to resolve it. In this article, we’ll explore the causes of run time error 1004, practical steps to fix it, and preventive measures to reduce the chances of it happening again. What […]
How To Parse A Flat File In Excel VBA
In another post I demonstrated how to access a file on your computer using the MS Office Library. Here it is if you don’t know what I’m talking about. In this post, I am going to show you how to access the file and load it into your spreadsheet. I will do the same thing […]
How to pick a file to load In VBA
How to Pick a File in VBA: FileDialog & GetOpenFilename Explained When building Excel VBA applications, you’ll often need to let users pick a file to load in VBA. Instead of hard-coding file paths, you can use built-in dialogs that make file selection easy and user-friendly. VBA offers two main approaches: FileDialog object (flexible, customizable) […]
How can I interact with other Office applications (Excel) using VBA in Access?
Need to write your Access data or query to an Excel file? Here is the how to do it: Most people are familiar with Excel and know how to use it well (enough), and when you start talking about Access, they get scared off, and don’t know what to do anymore. Well, here you are […]
Support these sponsors: