DOMAIN AGGREGATE FUNCTIONS
The easiest method to determine running sums uses a domain aggregate function called DSum. Domain aggregate functions retrieve data (expression) from tables or queries (domain) with optional conditions (criteria). Data is aggregated by sum, minimum, maximum, count, average lookup, first, last, standard deviation, or variance.
The syntax for DSum is DSum(expression, domain, [criteria]) where expression is the field or equation to aggregate; domain is the name of a table or select query; and criteria, which is optional, provides conditions that restrict the number of records evaluated. The domain is often different than the query source, but not always. In our example to create running sums, the domains will be the same because we are adding a field in the same table.
CREATE A RUNNING SUM
Make a new query using the ProdSales table from last month’s column. Display the Total row on the grid by clicking the Totals button on the Design ribbon. The Total row contains a dropdown with several options, including Group By, Sum, Avg, Min, Max, Count, StDev, Var, First, Last, Expression, and Where.
Figure 1 shows the fields and settings for the query. Enter Mnth: Format (dtSale,“mmm”) into the first field of the grid. A format code of “mmm” is used to convert a date into a three-character string from Jan to Dec. Mnth is the name of the calculated field. Leave the Total set to the default value of Group By.
In the second field, enter Sale15: Amount and choose Sum in the Total row. This sets the monthly sum to be called Sale15. In the third field, enter Yr: Year(dtSale), with the Total defaulting to Group By. The Year function takes a date and returns a year with four digits. We will use this Yr field, which is the calculated name for the report year, in the criteria for the DSum function.
The fourth field is Mo: Month(dtSale). The Total is Group By, and set the Sort to Ascending. The Month function returns a number between 1 and 12 from a date. This field, Mo, will also be used in DSum criteria.
The fifth field is the running sum calculation: RunSum: CCur(DSum(“Amount”, “ProdSales”, “Year(dtSale)=” & [Yr] & “ AND Month(dtSale)<=” & [Mo])). The expression for the DSum is the Amount field. The domain, where the records come from, is the ProdSales table. The criteria limits resulting records to dates in the report year that have a month number less than or equal to the report month.
Since the field we’re calculating the running sum for is a dollar amount, the DSum expression is wrapped in the CCur function to convert values to currency data type. RunSum is the name of the new running sum field. The Total row is set to Expression. Because Yr and Mo are defined on the grid, they can be used in the expression as if they appeared in the field list of the query source.
The final field is YrWhere: Year(dtSale), with the Criteria set to 2015, the Total set to Where, and the Show box unchecked so the field does not appear in the query results. You may wonder why this field was added rather than simply setting the Criteria of the Yr field to 2015. Because of the grouping, the criteria for the Yr field is applied after the aggregation, which means that other years would also be calculated before being eliminated by criteria, causing the query to take longer to compute and use more resources. By entering the 2015 criteria in a column using Where in the Total cell, the criteria is applied before the aggregate is done, enabling quicker and more efficient operation.
Save the query as “q_Sales_by_Month_2015_RunningSum” and switch to Datasheet view to see the results. Figure 2 shows the Datasheet View of the query with sales by month and a running sum.
SIMILAR DATE FUNCTIONS
There are other functions similar to Year and Month that return integer variants from a date, including Day to get a number from 1 to 31, Hour to get 0 to 23, Minute and Second for 0 to 59, and Weekday for 1 to 7.
Another useful function to get information from a date is DatePart(Interval, DateExpression, [FirstDayOfWeek], [FirstWeekOfYear]) to return the specified part of a date as an integer variant. The Interval argument gives DatePart a lot of flexibility and includes the ability to extract even more results. Options for the Interval argument include Day Of Year (“y” returns a value of 1 to 366), Quarter (“q” returns a value of 1 to 4), and Week Of Year (“ww” returns a value of 1 to 52).
USING VBA TO CALCULATE A RUNNING SUM
The same information given in the arguments for DSum can also be given to a user-defined VBA function to calculate running sum using a recordset. Names are hard‑coded, but they could be passed as parameters for greater flexibility.
In the query, the new equation for RunSum uses GetRunSum instead of DSum. RunSum: GetRunSum(Year(dtSale), Month(dtSale)) parses year and month out of the dtSale field to send as parameters. Since the VBA function returns a currency value, the query equation is simplified by not needing CCur.
In VBA, the function name and its parameters are declared. Statements ending with space underscore ( _ ) are continued on the next line. Green words are comments.
The error handler is set up. Object and simple variables are dimensioned. The default return value for the function is assigned. A Where clause is constructed using the passed parameters to limit records. A SQL statement is then built to sum the amount for the given criteria, and a recordset is opened with the results. The calculated value is assigned as the return value for the function. Object variables are then released, and control exits the function. Here’s the code:
Function GetRunSum( _
piYr As Integer _
, piMo As Integer _
) As Currency
'151230 strive4peace
'RETURNS
' sum of Amount in ProdSales table using dtSale for criteria
' where records are in the year passed
' and before or in the month number passed
'PARAMETERS
' piYr is the year to get records from
' piMo is the month number to get records on or before
'set up the error handler
On Error GoTo Proc_Err
'dimension object variables
Dim db As DAO.Database _
, rs As DAO.Recordset
'dimension simple variables
Dim sWhere As String _
, sSql As String
'default return value
GetRunSum = 0
'create the WHERE clause
sWhere = “Year(dtSale) = “ & piYr _
& “ AND Month(dtSale) <= “ & piMo
'construct SQL statement
sSql = “SELECT Sum(Amount) as SumAmount “ _
& “ FROM ProdSales “ _
& “ WHERE “ & sWhere _
& “;”
'set database and recordset objects
Set db = CurrentDb
Set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
'open recordset and read value
With rs
If Not .EOF Then
'assign return value for the function
GetRunSum = !SumAmount
End If
.Close
End With 'rs
Set rs = Nothing
Proc_Exit: 'exit code
On Error Resume Next
'release object variables
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function
Proc_Err: 'error handler
MsgBox Err.Description, , _
“ERROR “ & Err.Number _
& “ GetRunSum”
Resume Proc_Exit
Resume
End Function
See the database file for examples of calculating a running sum with DSum in a query and with VBA. Next month, we’ll add a series to a chart and display it on the secondary axis.
Calculating Running Sum 02-2016
SF SAYS
Fields defined in the query by design grid can be used in other expressions on the grid just as if they were fields in the query source.
February 2016
