Create query with a running total using Access 2010

frimy
frimy used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
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.
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
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
Distinguished Expert 2017
Commented:
I would think long and hard about doing this in a query at all.  For one thing, the solutions only work if you have a unique identifier and that unique identifier provides the sort order for the output.  If you need to do this on a non-unique field such as date, you should first summarize the recordset to produce a single row for each date.  Then the running sum will work.  If you are creating a report, there is no problem since a report is produced sequentially which is different from how the results of a query are produced.  If you need a running sum on a report, there is no need for a unique id.  You just need to sort the data on whatever makes sense to produce the running sum.  To produce a running sum on a report, use the RunningSum property of the Data tab.  The sum can be over all or over a group if you have used grouping in the report.

I haven't tested this with a table of any size but based on my understanding that Access does not optimize subqueries, I would think that Dale's join suggestion would be more efficient than using a subselect although both should be better than using DSum()

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial