Calculating query

[anzacks]

Hi,

I have table like below
x; y; z
-1.99811696; 0.129995; 4

I want to make a query which calculate the running sum of (x + y) until z value

runningsumx
-1.199811696
-1.069816403
-0.939821111
-0.809825819
-0.679830526
-0.549835234
-0.419839941
-0.289844649
....
4.0000000

Please help.

TQ

 

[JANR]

You could try and use a recursive function to populate a temp table and Inner Join this temp table with your table on a recordID.

Option Compare Database
Option Explicit

Function RunningSum(ByVal RecID As Long, ByVal StartNum As Double, ByVal Increment As Double, ByVal StopValue As Double)
'Recursive function
Dim i As Double
i = StartNum + Increment
       
    If i <= StopValue Then
        AddIncrement RecID, i
        Call RunningSum(RecID, i, Increment, StopValue)
    End If
End Function
 
Function AddIncrement(ByVal RecID As Long, ByVal i As Double)
'Function to populate a temptable with values from RunningSum function
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblTMP", dbOpenDynaset)
 
With rs
    .AddNew
    .Fields("ID") = RecID
    .Fields("Increment") = i
    .Update
    .Close
End With

Set rs = Nothing
End Function

See attached db

JR

 

[vbaInet]

A running sum can be easily done in a Report.

http://office.microsoft.com/en-us/a...running-sum-cumulative-total-HP005187388.aspx

By the way, what field(s) are you sorting on?

 

[anzacks]

Thansk JANR. That's was very useful.