Running Sum Total in Query question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a field in a query equal to the sum of a second field in the query so that it is a running total of all values up to and including the row where the sum is displayed. Sort of like this
Field1 Summing Fiel
10 1
20 3
30 6
40 10
Any ideas on how to do this
thanks

Winston Abernathy
 
Winston,

Are you into VB code? Well, if not, this is your chance to start!
Open any existing module, or add a new one, and declare a public variable in
the Declarations section (top of the screen, right after "Option Compare
Database", and before the first sub or function):
Dim Rsum as Double

Then copy and paste in the following code:

Function Reset_Running_Sum()
Rsum = 0
End Function

Function Running_Sum(MyVar As Double)
Rsum = Rsum + MyVar
Running_Sum = Rsum
End Function

Now go to your query design, and and add a field in the grid:
Running Sum: = Running_Sum([Field1])
(change Field1 to the actual field name)

Last, you need a macro as follows:
Action Argument
RunCode Reset_Running_Sum()
OpenQuery QueryName

The idea is that, instead of running the query directly, you do it through
the macro, so it first runs the code that resets the running sum value to 0,
and then it runs the macro (which calls the Running_Sum function in every
line).

HTH,
Nikos


Winston Abernathy said:
I would like to create a field in a query equal to the sum of a second
Click to expand...
field in the query so that it is a running total of all values up to and
including the row where the sum is displayed. Sort of like this:
 
Do you have a unique field (or combination of fields) that imposes the order on
your records. If so, it can be done in a query.

SELECT Field1,
(Select Sum(TT.Field1)
FROM TheTable as TT
WHERE TT.OrderByField <= TheTable.OrderByField) As RunningSum
FROM TheTable

Note that if your Order determining field is not unique then you are going to
get the same running sum for those records that are "tied".

Nikos said:
Winston,

Are you into VB code? Well, if not, this is your chance to start!
Open any existing module, or add a new one, and declare a public variable in
the Declarations section (top of the screen, right after "Option Compare
Database", and before the first sub or function):
Dim Rsum as Double

Then copy and paste in the following code:

Function Reset_Running_Sum()
Rsum = 0
End Function

Function Running_Sum(MyVar As Double)
Rsum = Rsum + MyVar
Running_Sum = Rsum
End Function

Now go to your query design, and and add a field in the grid:
Running Sum: = Running_Sum([Field1])
(change Field1 to the actual field name)

Last, you need a macro as follows:
Action Argument
RunCode Reset_Running_Sum()
OpenQuery QueryName

The idea is that, instead of running the query directly, you do it through
the macro, so it first runs the code that resets the running sum value to 0,
and then it runs the macro (which calls the Running_Sum function in every
line).

HTH,
Nikos

Winston Abernathy said:
I would like to create a field in a query equal to the sum of a second
Click to expand...
field in the query so that it is a running total of all values up to and
including the row where the sum is displayed. Sort of like this:
Field1 Summing Field
10 10
20 30
30 60
40 100
Any ideas on how to do this?
thanks,

Winston Abernathy
Click to expand...
Click to expand...
 
Back
Top