MS Access Functions

Create a Running Total in a Microsoft Access Query

Last updated: August 29, 2026.

A running total requires a stable row order. Dates alone are not enough when multiple records can share the same date, so include a unique key as the tiebreaker.

Running total across all sales

Join each row to every earlier row and to itself, then sum the matching amounts.

SELECT a.SaleID,
       a.SaleDate,
       a.Amount,
       Sum(b.Amount) AS RunningTotal
FROM Sales AS a
INNER JOIN Sales AS b
    ON (b.SaleDate < a.SaleDate)
    OR (b.SaleDate = a.SaleDate AND b.SaleID <= a.SaleID)
GROUP BY a.SaleID, a.SaleDate, a.Amount
ORDER BY a.SaleDate, a.SaleID;

Restart the total for each account

Add the account match to the join and group by the account.

SELECT a.AccountID, a.SaleID, a.SaleDate, a.Amount,
       Sum(b.Amount) AS AccountRunningTotal
FROM Sales AS a
INNER JOIN Sales AS b
    ON a.AccountID = b.AccountID
   AND ((b.SaleDate < a.SaleDate)
     OR (b.SaleDate = a.SaleDate AND b.SaleID <= a.SaleID))
GROUP BY a.AccountID, a.SaleID, a.SaleDate, a.Amount
ORDER BY a.AccountID, a.SaleDate, a.SaleID;

Use the report feature when possible

For display-only reports, Access can calculate a running sum in a report control without an expensive self-join. Use the query when later calculations or exports require the running value. Index AccountID, SaleDate, and the key for larger tables.

Choose query or report calculation

Use a query when the cumulative value must be exported, filtered, or reused in another calculation. For display-only Access reports, a Running Sum control is often simpler and faster than a self-join.

Verify the result with several rows sharing the same date and with a new account or category. The unique key must make the sequence deterministic, and partitioned totals must restart at the correct boundary.

  • Index the partition and ordering fields.
  • Exclude invalid dates before calculation.
  • Compare the final running value with an ordinary total.

Use realistic sample data that includes Nulls, ties, and boundary dates. Save a copy before converting a SELECT query into an action query, and compare row counts before relying on the result.

Continue with top records per group and monthly grouping.

Reference: Microsoft article about running sums in reports.

admin

admin

Related Posts