We help IT Professionals succeed at work.

Create query with a running total using Access 2010

frimy
frimy asked
on
134 Views
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
Comment
Watch Question

Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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")
rs.Close

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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Since,
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.

OR

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.