Link to home
Start Free TrialLog in
Avatar of frimy
frimyFlag for United States of America

asked on

Create query with a running total using Access 2010

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
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

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

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
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of frimy

ASKER

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.