CODEWITHSUNDEEP
ms-accessms-access-2010
ranopano
ranopano

Reputation: 539

Running Total in Access Query

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.

Upvotes: 1

Views: 943

Answers (1)

SandPiper
SandPiper

Reputation: 2906

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.

Upvotes: 1

Related Questions