We help IT Professionals succeed at work.

Create query with a running total using Access 2010

frimy asked
Last Modified: 2018-11-25
Hello All,

using MS Access  2010
how can i make a query with a running Total not using the Dsum?
Also can i make it from a query or it has to be from a table.

thank you in advance
Watch Question

Fabrice LambertConsulting
Distinguished Expert 2017

how can i make a query with a running Total not using the Dsum?
Dsum the shorter version of a SELECT query calling the Sum function.
The following codes provide the same thing:
Dim mySum as long.
mySum = DSum("myColumn", "myTable")

Open in new window

Dim sql As String
sql = vbNullString
sql = sql & "SELECT Sum(myColumn) As mySum" & vbCrLf
sql = sql & "FROM MyTable;"

Dim db As DAO.Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Dim mySum As Long
mySum = rs("mySum")

Open in new window

Also can i make it from a query or it has to be from a table.
Yes you can.
Queries can use tables, subqueries or other "SELECT" queries as data source.
Most Valuable Expert 2015
Distinguished Expert 2018

You can use a simple subquery in the query, like:

Select *, 
    (Select Sum(Amount) From [Orders] As T 
    Where T.Id = [Orders].Id And T.Date <= [Orders].Date) As TotalAmount
From [Orders]

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

or you could create a query like the following, which I find works quicker than the subquery, especially with large data sets.

SELECT T.ID, T.DateTime, T.Debit, T.Credit, SUM(NZ(T1.Debit, 0) + NZ(T1.Credit)) as Balance
FROM yourTable as T
INNER JOIN yourTable as T1 ON T1.DateTime<= T.DateTime
GROUP BY T.ID, T.DateDime, T.Debit, T.Credit
Distinguished Expert 2017
This one is on us!
(Get your first solution completely free - no credit card required)


Dale's works beautiful and fast (but it doesn't work from a Query, I don't have time to investigate for now)
Gustav's is also very good and simple but it's very slow with a lot of records.
Thank you very much

I have now a list from all customers the daily open balance by date (that is the RunningSum).
now I need to get only the largest Open Balance for any date for each customer.

I'm going to post another question.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.