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.

.