Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

As you have seen in the previous post, you can easily create a running total on a given set of data. A slight modification can be added to this by making MS Access re-start the running total at certain point as decided by you.

Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

In this post, you’ll learn how to do so….

Work Scenario

You have received a similar set of data that you received in the previous post. The only difference is that along with the list of products and their sales in dollars, you now also have the column category that identifies each product.

The data that you have received is as follows:

Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

Even here, you want to calculate the running total – BUT you want the tool to restart the running total for every category. i.e. you are expecting the result something like shown below:

Category

Product

Amount

Running Total

Category 1

Product 1

clip_image001[4]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

Calculating Running Total based on Groups /Category

To begin, you’ll follow the same 5 steps that you followed in the previous post (Part 1 0f 2) . The only difference being the way you construct the criteria part of the DSUM () function:

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 (MAKE TABLE Query)

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]”, “[Category]=’”&[Category]&”’ AND [No]<=”& [No])

Step 5

Run the Query in Step 4 and check the results

The query will be created as follows:

Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

Important : Please note the use of Multiple Criteria in the DSUM() function above. To learn more about it, please read the post : DSUM() function –The Art of Writing a Criteria : 1

On running the above query, you’ll get the following result.

Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2 

This way, you can get the desired output. Note that in this example, you have sorted the Amount in Descending Order.

You can similarly create the query by sorting the data in Ascending Order or by just keeping the Amount in as-is order or random order. You will have to make minor changes in the query – to refresh your memory please read the previous post. 

Summary

You have now learnt how to create a running total (cumulative sum) whether you are working on a simple list or on a grouped list where you want MS Access to re-start the running total at every new groups.

Homework

I would like you to practice this technique on a new set of data – preferably Northwind database. Practice this technique by keeping the data in all the three sort order – descending, ascending and as-is order.

Once you are done, share the learnings with all by posting comments.

Advertisements

11 responses to “Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

  1. Thank you dude. You just saved my ass.

  2. Thanks alot! Great help!

  3. I was able to do part one without a problem, but now it just won’t work! I’ve even created a table with the same names and values as you, and have copied the dsum exactly. I keep getting the error ‘The expression you have entered contains invalid syntax: You may have entered an operand without an operator’.

    Do you have an idea why that would be?

  4. Thanks for this great tutorial…..I get an error in my DSUM expression. Cant figure out why. The error is: ‘The expression you have entered contains invalid syntax: You may have entered an operand without an operator’.

    My formula is:

    RT: DSUM (“[Amount]”, “[01_Payments_PXHCRA]”, “[Patient]=’”&[Patient]&”’ AND [No]<=”& [No])

  5. I’m getting a slightly different error. “Syntax Error (missing operator) for the expression…”

    Formula:
    RT: DSum(“[Credit]”,”[tblTransactions]”,”[AccountID]=” & [AccountID] & ” AND[TransactionID]<=" & [TransactionID])

    Any thoughts?

  6. THANK YOU VERY MUCH FROM COLOMBIA!

  7. You really should reference https://theaccessbuddy.wordpress.com/2013/12/25/dsum-function-the-art-of-writing-a-criteria/

    I had no idea why you were trying to add quotation marks & single quotation marks into the mix but your other page actually explains what’s going on.

  8. Thank you, 🙂 Finally I was able to find the answer I so needed. There is a lot of conflicting information about the syntax of DSum criteria on the net and it was rather frustrating using all the samples and trying to make them work. This was clear and gave me no issues.
    I am modifying a check register that would work with multiple accounts on the main transactions table and needed a running total for individual accounts for auditing purposes. I can’t believe MS does not have a decent check register template.

  9. I found your article very helpful in correcting my DSum syntax. But after two successful runs my query is now locking Access up every time. I’ve compacted and restarted quite a few times.
    I’m using data at a college. I have a table with student ids (SID – text), terms (YRQ – text) and credits earned. We need to know the students’ cumulative credits for each term. There are 420,000 records in the table. My DSum query is:
    SELECT [t001: credits earned].SID, [t001: credits earned].YRQ, DSum(“[credit]”,”[t001: credits earned]”,”[SID] = ‘” & [SID] & “‘ And [YRQ] <= '" & [YRQ] & "'") AS [cum cr earned]
    FROM [t001: credits earned]
    ORDER BY [t001: credits earned].SID, [t001: credits earned].YRQ;
    Is DSum just the wrong tool for a table this size in Access?

  10. I figured out why i was getting an error with the above!!! See if it works for you. In this expression
    RT: DSUM (“[Amount]”, “[Table]”, “[Category]=’”&[Category]&”’ AND [No]<=”& [No])
    Make sure where you see three quote marks after the equal sign, you actually enter a single quote, then double quote, then after the & sign where you see three quote marks again, make sure you put the double quotes, and then a single quote. this fixed my issue. Good luck.

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