How to Create an Access Running Total with Multiple Criteria
Question details
The user needs to calculate a running total in a database grouped by specific components and ordered by date.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating a query or report to display ongoing balance calculations based on complex criteria like component ID and chronological date.
- Observed behavior
- The current DSum expression fails to apply the multiple criteria properly, and using domain aggregate functions causes extremely slow performance in large queries.
Ensure your database table includes a primary key (such as a TransactionID) to serve as a tie-breaker for calculations when multiple records share the exact same date.
Use an SQL Self-Join Query
This is the most efficient method for large queries, significantly outperforming domain aggregate functions like DSum.
Calculating a running total with a self-join involves joining two instances of the same table. You match rows by your component identifier and sum the values where the date of the second instance is less than or equal to the first.
In your Access database, navigate to the Create tab and select Query Design. Close the Show Table dialog.
Right-click the query tab and choose SQL View from the context menu.
Write a SELECT statement that joins the table (T1) to itself (T2). For example: SELECT T1.ComponentID, T1.Date, Sum(T2.Amount) AS RunningTotal FROM Table1 AS T1 INNER JOIN Table1 AS T2 ON T2.ComponentID = T1.ComponentID AND T2.Date <= T1.Date.
Add 'AND T2.PrimaryKeyID <= T1.PrimaryKeyID' to your join ON clause to prevent duplicate calculations when multiple transactions occur on the same day.
Add a GROUP BY clause that includes the ComponentID, Date, and PrimaryKeyID, then run the query to view your fast-loading running totals.

Apply Multiple Criteria to the DSum Function
Use this method if you need to stick to an expression-based calculated field and your dataset is relatively small.
Utilize Native Access Report Properties
If you only need to display the running sum rather than query it for other calculations, an Access Report is the most appropriate and straightforward tool.
Looking for a Free Alternative to Manage Your Data?
While Microsoft Access requires complex queries to aggregate data, you can quickly compute dynamic running balances without coding by using WPS Spreadsheet. Enjoy a highly compatible, feature-rich office suite tailored for modern data management.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Open Your Data: Launch WPS Spreadsheet and open your existing data files or paste in your database exports.
- 3. Calculate Totals Easily: Use simple SUM formulas with mixed relative and absolute references (e.g., =SUM($B$2:B2)) to instantly calculate running totals.

Frequently Asked Questions
Why is the DSum function so slow in my Access query?
Domain aggregate functions like DSum calculate row-by-row independently rather than utilizing the relational database engine's set-based operations. This overhead multiplies exponentially as your dataset grows, causing severe query lag.
How do I handle multiple transactions on the exact same date?
You need to add a unique identifier (like an AutoNumber Primary Key) to your criteria as a tie-breaker. In SQL, you would add `AND T2.ID <= T1.ID`, ensuring the system knows exactly which order to process records from the same day.
Can I format date variables in a DSum criteria string?
Yes. When building a criteria string in DSum, dates must be enclosed in hash marks (#) and should ideally be formatted to a standard like mm/dd/yyyy to avoid regional settings misinterpretation (e.g., `"[Date] <= #" & Format([Date], "mm/dd/yyyy") & "#"`).




