Others

How to Create a Running Sum in an Access Query, Form, or Report

Phi Hung Vo Oct 1, 2026 868 views

Question details

The user needs to calculate a continuous running balance for transactions in Microsoft Access and ensure correct ordering when multiple transactions share the exact same date.

How to Create a Running Sum in an Access Query, Form, or Report

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a financial or inventory report/query where a running total must be displayed row by row.
Observed behavior: Standard date-based calculations often group same-day transactions into a single end-of-day balance instead of a true per-transaction running sum.

Before you start

Ensure your database table includes a unique primary key (such as TransactionID) alongside your date field, as this will act as a necessary tie-breaker for transactions occurring on the same day.

Solution 1Recommended

Calculate Running Balance in Access Reports Using the RunningSum Property

The easiest way to calculate a running total in an Access report or continuous form is by utilizing the built-in RunningSum property of a text box.

This method is highly recommended for forms and reports because it requires no complex SQL coding and relies on native Microsoft Access UI features.

Open Design View

Right-click your Access report or continuous form in the Navigation Pane and select 'Design View'.

Select the Target Text Box

Click on the text box control where you want the running sum to be displayed.

Access the Property Sheet

Press F4 to open the Property Sheet pane on the right side of the screen. Navigate to the 'Data' tab.

Configure the Running Sum Property

Locate the 'Running Sum' property in the list. Change its value to 'Over All' to calculate a continuous balance throughout the entire report, or 'Over Group' to reset the total for each grouped category.

Calculate Running Balance in Access Reports Using the RunningSum Property

Solution 2

Create a Running Sum in an Access Query via Self-Join

For queries, a self-join calculation allows you to display running totals while using a unique ID to break ties for identical dates.

Solution 3

Calculate Totals Using the DSum Function

You can use the domain aggregate function DSum to calculate a running total directly within a query field, provided your date criteria are formatted properly.

Free Microsoft Office alternative

Manage Your Data Efficiently with WPS Office

While Microsoft Access is a powerful database tool, many running sum and data tracking tasks can be easily managed using spreadsheets. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office suites, featuring a powerful Spreadsheets application that handles complex formulas, pivot tables, and continuous data analysis with ease.

Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV formats.Easily calculate running totals using standard spreadsheet formulas like SUM with absolute references.Free, lightweight, and fast-loading alternative to traditional bulky Office suites.Familiar user interface makes migration seamless for Microsoft Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my running sum query only show an end-of-day balance?

If you only use the transaction date in your subquery or DSum criteria, Access groups all transactions that occurred on that same date together. To get a precise balance after every single transaction, you must include a unique primary key (like TransactionID) as a tie-breaker in your query criteria.

How do I fix date criteria syntax errors when using DSum?

Access requires date criteria in the DSum function to be formatted explicitly in a way the database engine understands. If you receive syntax errors, ensure your date is formatted as 'yyyy-mm-dd' inside the expression string. Example: `DSum("Total Trans", "tblData", "[Trans Date] <= #" & Format([Trans Date], "yyyy-mm-dd") & "#")`.

Can I use the CCur function for my running balance?

Yes, you can wrap your running total calculation in the CCur() function to explicitly convert the result into a currency data type. However, this is only necessary when the field must strictly be forced to display as currency; otherwise, standard table or text box formatting usually handles the display format correctly.

Office Quick Solutions