Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

While analysing data, there are many instances when you need to create a running total (also known as a ‘Cumulative Sum’). In this post, you will learn to create a running total using MS Access Query. Actually, if you know how a DSUM() function works, then creating a running total is very easy.

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

Let’s see how we can use this MS Access function to create a running total using just a query design grid.

Work Scenario

You have just received a raw data containing a list of products along with its sales volume in dollars. You are in the process of doing different analysis for which you need to create a running total.

There are three different ways you want to create a running total:

  1. Create a running total with products in descending order i.e. products with the highest sales volume will come first while the lowest will come last.
  2. Create a running total with products in ascending order i.e. products with the lowest sales volume will come first while the highest will come last.
  3. Create a running total in the given order of products (any random order).

What is a Running Total??

Running total is the sum of sequence of numbers in such a way that each new sequence of number is added to the sum of the previous sequence. A better way to understand this is by looking at the example below:

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

“One of the advantages of running total is that it helps you to know the total sales made so far at any point of time i.e. in the given example, since you are creating a running total you’ll know the total sales made so far every time a new entry is made”

In short, every time a new record is added, the sale of the new record is added to the sum of all previous records.

Understanding the Data

The data you have received is as follows:

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

As you can see, there are total 25 products along with their sales volume in dollars (titled ‘Amount’). The data is in a given random order i.e. the sales volume is neither in ascending order nor in descending order.

Running Total with Product Amount in “Descending Order”

Let us begin with the 1st point in the scenario.

In order to get the products in descending order, you create a Select query by selecting the two columns (fields) that you need. Then apply a descending sort on the column amount as shown in the figure below:

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

  1. Select the field Product in the query design wizard.
  2. Then select the field Amount and change the sort order to descending
  3. Add a third field and label it as RunTot. In this field, create a DSUM() expression as follows:

RT: DSUM(“[Amount]”, “[Table]”, “[Amount]>=”&[Amount])

At every record, the DSUM () examines the criteria and returns the sum of all the records that meet the criteria. So, on running the query you get:

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

However, there is an exception here. The above formula will NOT work when there are two products with the same value i.e. when there are duplicate values.

Let’s say that product 14 and product 15 now have equal values (i.e. duplicate values) as shown below:

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

On running the query on the table with duplicate values, you’ll get:

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

As you can see, the DSUM () will return the same result for both the product instead of calculating a cumulative sum or running total. What we really need is that DSUM should add the same amount to the previous total regardless of whether or not they are equal. This is happening because of the way the criteria has been written in the DSUM () function. The criteria make a reference to the same column that contains the duplicate value i.e. in this case it reference to the column [Amount] which also contains duplicate values, thereby returning misleading results.

Resolving the issue of Duplicate Values

In order to avoid this, you need to find a way to add a column that would contain unique numbers. Then, you can create a criteria such that it makes a reference to this column of unique numbers instead of referencing it to the column that contains the duplicate values. 

Now, in MS Access, there is no straight forward & simple way to uniquely number each row in a column. So, there’s a workaround which is explained as below:

  1. Create a simple query based on the given data with the field [Amount] in descending order. This is something which you have done earlier also. Save this Query as ‘Descending’ (or whatever name you like).
     
    Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2
  2. Now, create a new Table based on the above query as shown.This table will be created using a “Make Table” Query.

    Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

    Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

    Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

  3. On Clicking Run, you’ll get :
     Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2 
  4. Once you create the table, insert a new column titled ‘No’ and its data type as Autonumber. This will create a table with the same fields that you need along with an additional column that uniquely identifies each record.

    Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

    Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

  5. Once this table is created, then create another query based on this newly created table. The new query should include the following DSUM function.

RT: DSUM (“[Amount]”, “[Table]”, “[No]<=”& [No])

[Note: The column with Autonumber is in ascending order, hence we are using <= operator. If ever, you already have a unique number column that is descending order, then you can use >= operator in the criteria part of the DSUM () function.]

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

  {For more on DSUM function: Read DSUM () Part 1, Part 2 and Part 3}

On running this query, you get the following desired result :

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

So, in short, it is always a good practice to add a unique number column while you are creating a running total.

Running Total with Product Amount in “Ascending Order”

Creating a running total with products in ascending order is exactly same as descending order except for the comparison operator sign:

RT: DSUM (“[Amount]”, “[Table]”, “[Amount]<=”& [Amount])

At every record, the DSUM () examines the criteria and returns the sum of all the records that are less than or equal to the amount in current row.

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

Again, here too the query will return misleading results if there are duplicate values.

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

Hence, you need to create a new column that uniquely identifies each rows. So, you start by creating queries as mentioned below:

  1. Create a simple query based on the given data with the field [Amount] in ascending order now.
  2. Now, create a new Table based on the above query as shown. Once you create the table, insert a new column titled ‘No’ and its data type as Autonumber. This will create a table with the same fields that you need along with an additional column that uniquely identifies each record.
  3. Once this table is created, then create another query based on this newly created table. The new query should include the following DSUM function.

RT: DSUM (“[Amount]”, “[Table]”, “[No]<=”& [No])

[Note: The column with Autonumber is in ascending order, hence we are using <= operator. If ever, you already have a unique number column that is descending order, then you can use >= operator in the criteria part of the DSUM () function.]

On running this query, you get the following result as expected:

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

Running Total with Product Amount in “Random Order or As-Is Order”

When it comes to creating a running total with the product amounts in random order or as-is order in which they are received, then you need to have another column with unique numbers either in ascending or descending order.

In this case, now, you need to add a column with unique numbers as done previously. Please check the screenshots below:

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

In this way, you can create a running total regardless of their sort order or whether they have duplicate values or not.

Summary

In short, in order to create a running total you need to follow the steps given below:

Steps

Description

Step 1

Create a query based on the given table with the columns that needs to be summed either in descending, ascending or as-is order.

Step 2

Create a new table based on the query created in the Step 1

Step 3

Add a new column [No] with data-type Autonumber. Save the table.

Step 4

Create a New Query again based on the table created in Step 3 with the required columns in the query design grid and the formula:

RT: DSUM (“[Amount]”, “[Table]”, “[No]<=”& [No])

If the values in field [No] are in descending order, use the operator <=

If the values in field [No] are in ascending order, use the operator >=

Step 5

Run the Query in Step 4 and check the results.

[Note: The use of domain aggregate function DSUM () can make your query run slow depending on the number of records]

Next Steps

In the next post, you will learn how to create a running total when the products are categorized and you need to re-start the running total at each category. You want the result to look like as shown in the table below:

Category

Product

Amount

Running Total

Category 1

Product 1

100

100 (Start here)

 

Product 2

50

150

 

Product 3

40

190

 

Product 4

30

220

 

Product 5

5

225

Category 2

Product 1

200

200 (Re-start here)

 

Product 2

120

320

 

Product 3

100

420

Category 3

Product 1

50

50 (Re-start here)

 

Product 2

40

90

 

Product 3

30

120

 

Product 4

20

140

 

Product 5

10

150

Advertisements

10 responses to “Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

  1. Holy crap!!! Why the hell is this so complicated????

  2. Thank you….well done!bob

  3. Great, but how can I sum, if I only want to sum up last 12 records ?

  4. Could you correct this so that the field you are working with doesn’t have the same name as the table? It is hard to follow the logic when I don’t know if the name in the formula is referring to the PROD table or the PROD field. I know, I’m really kinda dumb but I’m trying to make this work.

  5. it seems not working for me
    can you please help?
    Table
    Copy Of 023b Total Invoice GlobalBySupp (*last column not included, as a check calculation post)

    TotSumInvNoGL SuppTotGL TotGL RTSuppInvTotGL *Expected RT
    1 371,300,865.10 9,441,717,529.81 371,300,865.10 371,300,865
    2 368,389,588.95 9,441,717,529.81 736,779,177.90 739,690,454
    3 229,306,011.51 9,441,717,529.81 687,918,034.53 968,996,466
    4 178,729,673.66 9,441,717,529.81 714,918,694.64 1,147,726,139
    5 170,457,194.98 9,441,717,529.81 852,285,974.90 1,318,183,334
    6 158,143,609.85 9,441,717,529.81 948,861,659.10 1,476,326,944

    as you can see the RT done in access is different from the right value (the expected RT column) calculated in excel

    query used in Access

    SELECT [Copy Of 023b Total Invoice GlobalBySupp].TotSumInvNoGL, [Copy Of 023b Total Invoice GlobalBySupp].SuppTotGL, [Copy Of 023b Total Invoice GlobalBySupp].TotGL, DSum([SuppTotGL],”[Copy Of 023b Total Invoice GlobalBySupp]”,”[TotSumInvNoGL] <=" & [TotSumInvNoGL]) AS RTSuppInvTotGL
    FROM [Copy Of 023b Total Invoice GlobalBySupp];

    access office 2016

    thanks

    Giulio

  6. Thanks for your great post
    very helpful

    I tried the RT DSUM it works in one case but not other

    could you help me?

    i am a bit lost

    data sample
    table:Copy Of 023b Total Invoice GlobalBySupp
    * not in table, check run after in excel

    TotSumInvNoGL SuppTotGL TotGL RTSuppInvTotGL *Expected RT
    1 371,300,865.10 9,441,717,529.81 371,300,865.10 371,300,865
    2 368,389,588.95 9,441,717,529.81 736,779,177.90 739,690,454
    3 229,306,011.51 9,441,717,529.81 687,918,034.53 968,996,466
    4 178,729,673.66 9,441,717,529.81 714,918,694.64 1,147,726,139
    5 170,457,194.98 9,441,717,529.81 852,285,974.90 1,318,183,334
    6 158,143,609.85 9,441,717,529.81 948,861,659.10 1,476,326,944

    access query used

    SELECT [Copy Of 023b Total Invoice GlobalBySupp].TotSumInvNoGL, [Copy Of 023b Total Invoice GlobalBySupp].SuppTotGL, [Copy Of 023b Total Invoice GlobalBySupp].TotGL, DSum([SuppTotGL],”[Copy Of 023b Total Invoice GlobalBySupp]”,”[TotSumInvNoGL] <=" & [TotSumInvNoGL]) AS RTSuppInvTotGL
    FROM [Copy Of 023b Total Invoice GlobalBySupp];

    as you can see RT in MS access does not match the one

    help greatly appreciated!!!

    thanks in advance

    best

    Giulio

  7. Thanks for your great post
    very helpful

    I tried the RT DSUM it works in one case but not other

    could you help me?

    i am a bit lost

    data sample
    table:Copy Of 023b Total Invoice GlobalBySupp
    * not in table, check run after in excel

    TotSumInvNoGL SuppTotGL TotGL RTSuppInvTotGL *Expected RT
    1 371,300,865.10 9,441,717,529.81 371,300,865.10 371,300,865
    2 368,389,588.95 9,441,717,529.81 736,779,177.90 739,690,454
    3 229,306,011.51 9,441,717,529.81 687,918,034.53 968,996,466
    4 178,729,673.66 9,441,717,529.81 714,918,694.64 1,147,726,139
    5 170,457,194.98 9,441,717,529.81 852,285,974.90 1,318,183,334
    6 158,143,609.85 9,441,717,529.81 948,861,659.10 1,476,326,944

    access query used

    SELECT [Copy Of 023b Total Invoice GlobalBySupp].TotSumInvNoGL, [Copy Of 023b Total Invoice GlobalBySupp].SuppTotGL, [Copy Of 023b Total Invoice GlobalBySupp].TotGL, DSum([SuppTotGL],”[Copy Of 023b Total Invoice GlobalBySupp]”,”[TotSumInvNoGL] <=" & [TotSumInvNoGL]) AS RTSuppInvTotGL
    FROM [Copy Of 023b Total Invoice GlobalBySupp];

    as you can see RT in MS access does not match the one

    help greatly appreciated!!!

    thanks in advance

    best

    Giulio

  8. sorry for the duplicates… first it did not show any post and then 3 appeared!!! I can provide sample files if needed

  9. Great job! Thank You!

  10. The running total coding and syntax is incorrect. The syntax should be DSum(“[Amount]”,”Prod”,”[Amount]<=" &[Amount]) The Table name does not have the brackets. The method does not look sound. I would use an Index ID such as [Prod_ID], make it ascending order, and code the Run Total as DSum("[Amount","Prod","[Prod]![Prod_ID]<=" &[Prod_ID])

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s