Running Sum VBA Code

[DanKay1]

I would like data in Table1 to compile running sum into table2.
Is there a running sum query in access? or it has to be done though VBA?


Table1: with the following fields
Key Commissions Sales


Table2:
Key RunningSumofCommissions RunningSumofSales

Thanks in advance.

 

[Remou]

Do you have a field on which to order the records?

Here is an example:

SELECT b.Updated, Sum(b.Result) AS SumOfResult, 
   (SELECT Sum([Result]) 
    FROM Statistics a 
    WHERE a.Updated<=b.Updated) AS RunningSumOfResult
FROM Statistics b
WHERE (((b.Code)="ABC"))
GROUP BY b.Updated

http://lessthandot.com

 

[DanKay1]

Remou thanks for your help, but its to slow, since I have a lot of records.

Do you have a VBA solution?

 

[PHV]

Do you have a VBA solution?
What have you tried so far and where in your code are you stuck ?

FYI, storing calculated/derived values is usually a bad choice:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DanKay1]

PHV, Thanks for reference guide, but i am familiar with relational databases.

my Query, but it doesnt work properly.

SELECT Table1.Date, Table1.Cost, DSum("[Cost]","table1",[Date]<=#1/2/2009#) AS [Running Sum]
FROM Table1;

 

[PHV]

What about this SQL code ?
SELECT A.Date, A.Cost, Sum(B.Cost) AS [Running Sum]
FROM Table1 AS A INNER JOIN Table1 AS B ON A.Date >= B.Date
GROUP BY A.Date, A.Cost

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

90 times out of 100 you can not write a VBA solution faster than SQL. By VBA there are two ways to do this. One is build a custom function that uses domain aggregate functions or recordsets. You can then use the function as a calculated field in a query. If SQL is slow that idea will be really really slow using. The other way would be to read your table and then write to a table, what I think you are suggesting. This should be faster, but I can not beleive faster than a sql solution

Here is example with Northwind. I do not recommend because you are saving calculated data, but sometimes with large data sets it is faster to save persistent data and update when necessary.

Public Sub createRunningSum()
  Dim rsSum As DAO.Recordset
  Dim rsOD As DAO.Recordset
  Dim tempID As Integer
  Dim tempSum As Currency
  Dim orderID As Integer
  Dim curPrice As Currency
  Dim curSum As Currency
  Set rsOD = CurrentDb.OpenRecordset("qryOrderDetails")
  Set rsSum = CurrentDb.OpenRecordset("tblRunningSum", dbOpenDynaset)
  Do While Not rsOD.EOF
    orderID = rsOD!orderID
    curPrice = rsOD!UnitPrice * rsOD!Quantity
    rsSum.AddNew
      If Not orderID = tempID Then
        tempSum = 0
      End If
        rsSum!orderID = rsOD!orderID
        rsSum!RunningSum = curPrice + tempSum
        tempSum = curPrice + tempSum
    rsSum.Update
    tempID = orderID
    rsOD.MoveNext
  Loop
End Sub

 

[DanKay1]

Magical...working perfectly.

Thanks.

 

[Remou]

PHV
As a matter of interest, the suggested SQL really kills an Access 2000 database when run against a table with 14K records. It is fine without the sum and grouping.

http://lessthandot.com

 

[PHV]

Remou, this is probably why sometimes it isn't a so bad idea to store derived/calculated values ...