MS Access Functions

Create a Running Total in a Microsoft Access Query

Last updated: August 28, 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.

Reference: Microsoft article about running sums in reports.

admin

admin

Related Posts