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
LVL 1
frimyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
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.
Gustav BrockCIOCommented:
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, Developing Solutions LLCCommented:
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
PatHartmanCommented:
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()

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frimyAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.