MS Access Running Count With String Column And Duplicates

In my previous post, http://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:

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


 

Free! Subscribe To Our YouTube Channel!

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather
Tags: , , , , , ,




 

Call Erik Now