1

I need to add a "Running Total" column in my Access Query. The purpose of this is to identify how many open locations there are at a given time.

There is a field called "Open Date" that I would like to be counted in Ascending Order.

In some instance, multiple locations open the same day as seen below:

╔══════════╦═══════════╦═══════════════╦
║ Location ║ Open Date ║ Running Total ║  
╠══════════╬═══════════╬═══════════════╬
║        1 ║ 1/1/1990  ║             1 ║
║        2 ║ 1/3/1990  ║             2 ║
║        5 ║ 1/3/1990  ║             3 ║
║        3 ║ 2/18/1991 ║             4 ║
║        6 ║ 3/17/1992 ║             5 ║
║        4 ║ 4/1/1995  ║             6 ║
╚══════════╩═══════════╩═══════════════╩

So in this instance, I can say on 4/1/1995, there were 6 open locations.

I've tried using DSum and DCount, but neither of these give the result I wanted.

UPDATE:

Here is the code I am currently using:

SELECT t1.[store sort], t1.[soft opening],
    (SELECT COUNT(t2.[store sort]) FROM [storelist query] as  t2 
     WHERE Format(t2.[soft opening], "yyyy-mm-dd") & "-" & t2.[store sort] <= 
           Format(t1.[soft opening], "yyyy-mm-dd") & "-" & t1.[soft opening]) AS Running_Total
FROM  [storelist query] as t1
ORDER BY Format(t1.[soft opening], "yyyy-mm-dd") & "-" & t1.[store sort];

However, here is the output:

+------------+--------------+---------------+
| store sort | soft opening | Running_Total |
+------------+--------------+---------------+
|          1 | 8/1/1980     |             1 |
|         10 | 4/1/1985     |             2 |
|          2 | 10/1/1986    |             2 |
|          3 | 4/1/1987     |             4 |
|          4 | 10/1/1987    |             4 |
|          5 | 3/1/1988     |             5 |
+------------+--------------+---------------+

Note: This is using the data. The previous example was just sample data I used for demonstration purposes.

As you can see, this isn't quite the desired effect.

In this instance, Store 2 should having a running total of 3 because it's the 3rd store. Store 4 should have a running total of 5 stores. And store 5 should having a running total of 6 stores, etc.

ranopano
  • 509
  • 2
  • 16
  • 32
  • 1
    Frequently asked question. Review https://support.microsoft.com/en-us/help/290136/how-to-create-a-running-totals-query-in-microsoft-access. Search forum, probably find examples. Here is one https://stackoverflow.com/questions/44443253/loop-through-records-and-increment-value-in-vba-access/44444821#44444821 – June7 Jun 14 '17 at 00:50
  • A search for `[ms-access] running total` returns even more results, even a couple questions titled almost exactly like yours: [Running total query in access](https://stackoverflow.com/questions/38936340/running-total-query-in-access) – C Perkins Jun 14 '17 at 04:42
  • Typo strikes again! See the comment on my answer for the resolution to your update. – SandPiper Jun 15 '17 at 22:32

1 Answers1

1

Unfortunately, MS Access does not have any of the very useful functions built into it like Oracle and others do. This is a perfect application of partitioning using RANK on those systems.

Fortunately for you, I have had to use this same kind of work around before and have a solution for you:

SELECT t1.[Location], t1.[Open Date],
    (SELECT COUNT(t2.[Location]) FROM My_Table t2 
     WHERE Format(t2.[Open Date], "yyyy-mm-dd") & "-" & t2.[Location] <= 
           Format(t1.[Open Date], "yyyy-mm-dd") & "-" & t1.[Location]) AS Running_Total
FROM My_Table t1
ORDER BY Format(t1.[Open Date], "yyyy-mm-dd") & "-" & t1.[Location];

It looks ugly, but I tested it in Access with your sample data and it works. Essentially, it runs an inline query against the same table you are selecting against and checks for how many primary keys are less than or equal to the current record. In this case, I had to derive a primary key from the date and location, but if you have another key you didn't show there is no reason that would not work too. It just has to be completely unique.

SandPiper
  • 2,816
  • 5
  • 30
  • 52
  • Hi thanks for the response! I just added an update under my original post. Would you mind having a look at it? The code is not working as intended. – ranopano Jun 15 '17 at 19:22
  • 1
    The beautiful thing about code is it always does exact what you tell it to! After looking closer at your code, you have `WHERE Format(t2.[soft opening], "yyyy-mm-dd") & "-" & t2.[store sort] <= Format(t1.[soft opening], "yyyy-mm-dd") & "-" & t1.[soft opening]) AS Running_Total`. Notice that the right side of that equality has `& "-" & t1.[soft opening])`, and it should actually be `& "-" & t1.[store sort])`. That will fix your problem. – SandPiper Jun 15 '17 at 22:31
  • IT works! Thanks so much! As a sidenote, the list of stores is quite long (about 1000 stores and growing daily). As a result, this query take a long time to run, particularly if I'm joining other queries to this newly created query. Is this the best way about to go about it? Would using VBA be better? – ranopano Jun 15 '17 at 22:40
  • That is the unfortunate side effect of Access not including a Row_Number or Rank function. Because it is an inline query, your run times will get longer and longer as the data grows. If the data is completely static and never changes, you might want to save it directly into its own field in the table. However, if you ever need to see a running total of a subset, that would not work. What do you need the running total for? – SandPiper Jun 15 '17 at 23:38