Technology |
ACCESS: RUNNING TOTALS
By
5 comments
From time to time, you might need to keep a running total in Access, especially if you’re working with financial or accounting data. It’s easy to create a running total within a report, but it’s more difficult to accomplish within a query.
For a report, you can simply set the Running Totals property of the appropriate text box to Overall. But if you want to, say, use the running totals elsewhere, calculate an account balance, or make a table with the running totals, you’ll need to have the running total data available in a query.
DSUM
One method is to use the DSUM function, which is a function that will calculate a sum of records in a table or query. It has three arguments:
- An expression that identifies the numeric field whose values you want to sum. It can be a string that identifies a field in a table or query, or it can be a calculation of the data in that field. You can use the name of a field in a table, a control on a form, a constant, or a function.
- A domain that identifies the set of records where the data is. It’s a string expression that can be a table or query name.
- The criteria that’s used to identify the range of data for the function to use. It’s also a string expression. Any field that’s included in the criteria must also be in the domain.
The expression and domain arguments are required. The criteria argument is optional.
EXAMPLE CASE
A good example of this strategy is calculating an account balance from debits and credits. Using the Transaction table in our database, we will use the DSUM function to calculate running totals for Debits and Credits and then subtract them to get the running total. Then we will use it to calculate the percent of total for the Debits.
Account Balance
The first step is to get the account balance. Create a query using the Transaction table as the data source. Add ID, Debit, and Credit to the design grid. In the fourth column, enter DebitRunningSum:DSum(“Debit”,“Transaction”,“ID <=” & [ID]) into the field. This will sum the Debit field in the Transaction table where the ID is less than or equal to the ID. This ensures the items are summed in order to get the running total. Run the query to make sure the totals are correct. Because of the function’s specific syntax, sometimes it can be a challenge to get all the quotes, brackets, and parentheses in the right spot.
In the next field, add CreditRunningSum: DSum(“Credit”,“Transaction”,“ID <=” & [ID]). This will sum the Credits field. Run the query again to test that the field is totaling properly.
Finally, add Balance: [CreditRunningSum]-[DebitRunningSum] in the next field. This will subtract the Credits from the Debits, yielding the balance. Test the query, then save it as “Running Sum Balance” and close it. See Table 1 for a list of the fields included in the query.
Total
Next, before we are able to create a query to calculate the percent of total of the Debits, we first need to create a query to get the total. This is a simple query that sums Debit. Create a query with the Transaction table as the source, and add the Debits field. Click the Totals button so the line appears in the design grid, and set it to Sum. Save the query as “Total.”
Running Totals and Percent Total
Now we’re ready to calculate the running totals and the percent of total. Create a query using both the Transaction table and the Total query as data sources. Add ID, Date, and Debit fields to the query. Set the Criteria for the Debit field to “Is Not Null.” In the next field, add DebitRunningSum:DSum(“Debit”,“Transaction”,“ID <=” & [ID]). Test the query to make sure all the details are right. You also could copy this syntax from the first query we made.
In the next field, enter PercentTotal: DSum(“Debit”,“Transaction”,“ID <=” & [ID])/[SumOfDebit]. This will divide the running totals by the overall total from the Total query. On the Property Sheet, set the field’s format property to Percent.
Save the query as “Running Sum Percent Total.” Test and close the query. Table 2 shows the fields and criteria used for this query.
NEXT MONTH
I am retiring from writing this column at the end of the year, which means next month will be my final column. It has been my privilege sharing Access strategies with you for these last seven years. I have been thinking about what to write in the final column, and as my final guidance for you, I gathered together a set of strategies from the last seven years to help as you move forward in your use of this powerful tool.
Download this month’s database here: SF_NOV_2015.SF_NOV_2015
SF BEST PRACTICES
When using a new function for the first time, be sure to understand the arguments clearly and apply them to the data in your tables and queries. Test thoroughly and keep trying until the function works.
This topic is very interesting for me.
The running balance in the example shows #error on firs line due to null value of the debit or credit.
my solution was to insert iif statements with an isnumeric function that replace a null debit or credit running total with 0 (zero) like this:
Balance: IIf(IsNumeric([CreditRunningSum])=0,0,[CreditRunningSum])-IIf(IsNumeric([DebitRunningSum])=0,0,[DebitRunningSum])
there may be a more efficient fix.
This doesn’t work in 2016
thanks
Thank you