Monday, January 3, 2011

Domain Function Example: Running Sum with DSum


So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.


Other Examples

  1. Simulate AutoNumber with DMax
  2. Numbered Query with DCount
  3. Difference Between with DMax
  4. Rolling Average with DAvg and DCount
  5. Begin Date and End Date from Effective Date

Running Sum

One thing that is easy to do in a report, but difficult to produce in a query, is a Running Sum. A running sum adds the value of a field in a record to the value of the same field in the previous record.

In a report, there are two common types of running sums: Over All and Over Group. To create a running over all, I place a textbox control on the details section, setting the Control Source property to the field I want summed and setting the Running Sum property to Over, as in Figure 1. Figure 2 shows the results. To create one over a group (say each Order number), I'd set the Running Sum property to Over Group

Figure 1

Figure 2

But it's not so easy in a query. The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record above the one you're on. The only way to do it is to somehow identify the previous record in terms of a Where condition. Since this Where condition must be evaluated for each line, I can do this with a domain aggregate function (DSum) is ideal.

For this method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, OrderDetailsID fits the bill.

There are two different ways I might want to see the running sum:
  1. Over the whole list (see Figure 3)
  2. Over each group, where the running sum resets to zero as the Order ID changes (see Figure 5).
Running Sum Over All

There are two methods to create a running sum: DSum domain aggregate function and a correlated subquery. I'm going to concentrate on the DSum method here.

DSum

SELECT OrderDetailID, OrderID, ProductID, Price,
DSum("Price","tblOrderDetails","OrderDetailID <=" & [OrderDetailID]) AS RunningSum
FROM tblOrderDetails;


The DSum function works much the same as the DCount in the Numbered Query example, but instead of counting the records, it sums them. Unfortunately, the DSum does not return a formatted number.

Figure 3

So if you are summing a currency field, you'll have to apply the formatting yourself. To do that, we can modify the DSum function adding the Format function to display the number as currency. Like so:

Format(DSum("Price","tblOrderDetails","OrderDetailID <=" & [OrderDetailID]), "Currency") AS RunningSumFormatted

Figure 4

Running Sum Over Group

In order to get the running sum for each grouping of OrderID, all I need to do is add another condition to the "Where" argument of the DSum:

DSum("Price","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID <=" & [OrderDetailID]) AS RunningSum

In this case, "OrderID = " & [OrderID]

The complete SQL statement (including formatting):

SELECT OrderDetailID, OrderID, ProductID, Price,
Format(DSum("Price","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID <=" & [OrderDetailID]),"Currency") AS RunningSum
FROM tblOrderDetails;



Figure 5

Subquery Method

Both types of running sum can also be done with correlated subqueries. I may discuss this at a later date. However, you can find both methods on my website, in this sample: RunningSumInQuery.mdb.
.

10 comments:

David F. Horovitz said...

Thank you for this. I was able to get this to work on a Tabular form but if I apply a filter to the form, it does not give the running sum for the records on the filter. It still gives the running sum for all records in the data file. Example, I have a field called fld_account for account names and if I apply the filter for this field to give the transactions for one particular account name it will not give the running sum for just those account. I would be grateful for your suggestion to david.horovitz68@gmail.com
Many thanks

Anonymous said...

Thanks ! Thanks! Thanks ! This saved my day

RyanM said...

Thank you for this great example. I am in need of 1 additional step and have not been able to figure it out. I have a running sum of the usage where I need to take a starting number called OnHand and subtract the Usage via the Running Sum. In Excel the formula looks like this:
=OnHand-Usage where the starting balance Onhand is at the top of the column and begins subtracting the usage for each day.

nirmala basuki said...

Thanks so much, very helpfull

Hannah said...

Hi,

I don't know if this blog is still active, but I'm trying to create a running sum using DSum, for a work project. The structure of the problem is the same as your example,in fact I've just copied and pasted the code, changing only the field names,but I can't get it to work. When I run it as a select query the running sum column returns errors, and when I run it as an update query (my preferred option)I get an error message saying "didn't update [all] fields due to a type conversion failure." I've changed all working fields to type long integer, so this shouldn't be a problem, but it keeps recurring. From google I can see that this is a somewhat common problem, but can't find a clear solution. Do you know if there's a way of fixing this? It's a very large table- 19112509 rows.

Roger Carlson said...

This isn't the best venue for support, but try it without the Format function:

SELECT OrderDetailID, OrderID, ProductID, Price,
DSum("Price","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID <=" & [OrderDetailID]) AS RunningSum
FROM tblOrderDetails;

If it still doesn't work, I suggest asking over at www.utteraccess.com

Anonymous said...

Thanks for not just recopying the same material of DSum that I seen many times. This is much clearer; therefor, I understand it more.

Unknown said...

Thank you so much! You totally saved my day, have searched numerous forums looking for how to create a running sum in my report. I found the DSum function difficult to use, regarding setting correct criteria. This textbox-trick in the report is awesome and much easier to use! Thanks a million! =)

Regards, Tom-Inge Nilsen, Harstad/Norway.

Unknown said...

Thanks a lot, you gave me the right syntax of Dsum !
You saved my day !

Unknown said...

Thanks a lot, you gave me the right syntax of Dsum.
You saved me day !