RunningSum Calculations

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

Guest

I'm using Access 2000. I'm trying to emulate the running sum of a text box
control property that exists if you are designing a report on a form. For
example,

Items IN Items OUT Running Sum
20 10 10
0 5 5
30 8 27

if I input into the Sum's text box Control Source property
=Sum(nz([UnitsIn])-nz([UnitsOut])), I get the total of all records in each
Sum text box. I tried programming it in VBA but get the same. Any help...

Don
 
If you using that in a report then in the property of the field with the
running sum,
select RunningSum = true
so you can get rid of the sum(...)
just write =nz([UnitsIn])-nz([UnitsOut])
and the running sum you defined before will do the rest.
 
Ofer,
If this were a report, I would not have this problem. In fact I have a
report on the data that does exactly what I want as displayed below. I am
trying to implement this on a subform with 1 to Many relationship. The
subform is basically a continuous forms and the users want a running sum as
they input transactions to the header record. I have already tried what you
said, i.e. =nz([UnitsIn])-nz([UnitsOut]) to an unbound Text Box. That
returns the following...

Items IN Items OUT Running Sum
20 10 10
0 5 -5
30 8 22

I also tried coding this like such to a bound Text Box's AfterUpdate event...
Dim intTTLSum as Long
intTTLSum=0
intTTLSum=intTTLSum+(nz([UnitsIn])-nz([UnitsOut]))
TTLSum=intTTLSum

then I get,

Items IN Items OUT Running Sum
20 10 20
0 5 15
30 8 37

Understand the delima here. I would sure like to know why MS put the
RunningSum property in the Reports but left it out of the forms??????

Tks,
Don

Ofer said:
If you using that in a report then in the property of the field with the
running sum,
select RunningSum = true
so you can get rid of the sum(...)
just write =nz([UnitsIn])-nz([UnitsOut])
and the running sum you defined before will do the rest.

Donald King said:
I'm using Access 2000. I'm trying to emulate the running sum of a text box
control property that exists if you are designing a report on a form. For
example,

Items IN Items OUT Running Sum
20 10 10
0 5 5
30 8 27

if I input into the Sum's text box Control Source property
=Sum(nz([UnitsIn])-nz([UnitsOut])), I get the total of all records in each
Sum text box. I tried programming it in VBA but get the same. Any help...

Don
Click to expand...
Click to expand...
 
Donald, I don't have time to write the code, but here is my idea.

You make a function that returns the running sum. This function is called
whenever you change an entry, i.e., On_Open for the form and On_Change events
for items IN and items OUT.

Public Function RunningSum() as integer
Dim nSum as Integer
Dim rstRecordSet as ADODB.Recordset ' or whatever type you want to use

' open the recordset
With rstRecordSet
.MoveFirst
While Not .EOF
nSum = nSum + Infield
nSum = nSum = Outfield
.MoveNext
Wend
RunningSum = nSum
End Function

In the field On_Change or On_Exit events, type in =RunningSum()

This is quick and dirty, but may point you in the direction you need

John H W

Donald King said:
Ofer,
If this were a report, I would not have this problem. In fact I have a
report on the data that does exactly what I want as displayed below. I am
trying to implement this on a subform with 1 to Many relationship. The
subform is basically a continuous forms and the users want a running sum as
they input transactions to the header record. I have already tried what you
said, i.e. =nz([UnitsIn])-nz([UnitsOut]) to an unbound Text Box. That
returns the following...

Items IN Items OUT Running Sum
20 10 10
0 5 -5
30 8 22

I also tried coding this like such to a bound Text Box's AfterUpdate event...
Dim intTTLSum as Long
intTTLSum=0
intTTLSum=intTTLSum+(nz([UnitsIn])-nz([UnitsOut]))
TTLSum=intTTLSum

then I get,

Items IN Items OUT Running Sum
20 10 20
0 5 15
30 8 37

Understand the delima here. I would sure like to know why MS put the
RunningSum property in the Reports but left it out of the forms??????

Tks,
Don

Ofer said:
If you using that in a report then in the property of the field with the
running sum,
select RunningSum = true
so you can get rid of the sum(...)
just write =nz([UnitsIn])-nz([UnitsOut])
and the running sum you defined before will do the rest.

Donald King said:
I'm using Access 2000. I'm trying to emulate the running sum of a text box
control property that exists if you are designing a report on a form. For
example,

Items IN Items OUT Running Sum
20 10 10
0 5 5
30 8 27

if I input into the Sum's text box Control Source property
=Sum(nz([UnitsIn])-nz([UnitsOut])), I get the total of all records in each
Sum text box. I tried programming it in VBA but get the same. Any help...

Don
Click to expand...
Click to expand...
Click to expand...
 
Better yet.

In the Running Sum field, name the field something like fmRunningSum.

Then in the RunningSum field, add a line fmRunningSum = nSum. Then change
the function to a sub (don't have it returning anything). You would still
call it from the event fields as I said before.

John H W

Donald King said:
Ofer,
If this were a report, I would not have this problem. In fact I have a
report on the data that does exactly what I want as displayed below. I am
trying to implement this on a subform with 1 to Many relationship. The
subform is basically a continuous forms and the users want a running sum as
they input transactions to the header record. I have already tried what you
said, i.e. =nz([UnitsIn])-nz([UnitsOut]) to an unbound Text Box. That
returns the following...

Items IN Items OUT Running Sum
20 10 10
0 5 -5
30 8 22

I also tried coding this like such to a bound Text Box's AfterUpdate event...
Dim intTTLSum as Long
intTTLSum=0
intTTLSum=intTTLSum+(nz([UnitsIn])-nz([UnitsOut]))
TTLSum=intTTLSum

then I get,

Items IN Items OUT Running Sum
20 10 20
0 5 15
30 8 37

Understand the delima here. I would sure like to know why MS put the
RunningSum property in the Reports but left it out of the forms??????

Tks,
Don

Ofer said:
If you using that in a report then in the property of the field with the
running sum,
select RunningSum = true
so you can get rid of the sum(...)
just write =nz([UnitsIn])-nz([UnitsOut])
and the running sum you defined before will do the rest.

Donald King said:
I'm using Access 2000. I'm trying to emulate the running sum of a text box
control property that exists if you are designing a report on a form. For
example,

Items IN Items OUT Running Sum
20 10 10
0 5 5
30 8 27

if I input into the Sum's text box Control Source property
=Sum(nz([UnitsIn])-nz([UnitsOut])), I get the total of all records in each
Sum text box. I tried programming it in VBA but get the same. Any help...

Don
Click to expand...
Click to expand...
Click to expand...
 
Donald said:
I'm using Access 2000. I'm trying to emulate the running sum of a text box
control property that exists if you are designing a report on a form. For
example,

Items IN Items OUT Running Sum
20 10 10
0 5 5
30 8 27

if I input into the Sum's text box Control Source property
=Sum(nz([UnitsIn])-nz([UnitsOut])), I get the total of all records in each
Sum text box. I tried programming it in VBA but get the same. Any help...
Click to expand...


I've never used these in a production application, but
here's a couple of functions from a test form:

Private Function LineNum(Key As Long) As Long
With Me.RecordsetClone
If .RecordCount = 0 Then Exit Function
.FindFirst "ID=" & Key
LineNum = .AbsolutePosition
End With
End Function

Private Function RunSum(Key As Long, FieldName As String) As
Long
With Me.RecordsetClone
If .RecordCount = 0 Then Exit Function
.MoveFirst
Do Until .Fields("ID") > Key
RunSum = RunSum + .Fields(FieldName)
.MoveNext
Loop
End With
End Function

The Key argument is the unique field that is used to oreder
the records in the form. The FieldName argument is the name
of the field you want to calculate the running sum. It
would be called from the running sum text box's
ControlSource expression:
=RunSum([ID], "Amount")

You will have to modify it to calculate the difference of
your two fields.

I hope your form doesn't have many records because this is
not going to be fast.
 
Hi, Donald

A useful article on the MS web-site is
http://support.microsoft.com/kb/13891
This is for Access97, but either the principle will apply to 2000, o
there may be another article specific to 2000

Anyway, I have sucessfully used this method, which uses a query. I
takes a bit of reading & understanding, though, and I'm sure it
not the fastest way to do it! However, it works
Donald Kingwrote
Click to expand...
I'm using Access 2000. I'm trying to emulate the running sum of
text box
control property that exists if you are designing a report on form. For
example

Items IN Items OUT Running Su
20 10 1
0 5
30 8 2

if I input into the Sum's text box Control Source property
=Sum(nz([UnitsIn])-nz([UnitsOut])), I get the total of all record in each
Sum text box. I tried programming it in VBA but get the same. An help..

Do
Click to expand...
 
Graham/Marshall/John,
Thanks for all the great info. I'm able to work on this project this
afternoon so I am sure I will be able to come up with a solution.

Tks,
Don

GrahamT said:
Hi, Donald.

A useful article on the MS web-site is:
http://support.microsoft.com/kb/138911
This is for Access97, but either the principle will apply to 2000, or
there may be another article specific to 2000.

Anyway, I have sucessfully used this method, which uses a query. It
takes a bit of reading & understanding, though, and I'm sure its
not the fastest way to do it! However, it works!
Donald Kingwrote:
Click to expand...
I'm using Access 2000. I'm trying to emulate the running sum of a
text box
control property that exists if you are designing a report on a form. For
example,

Items IN Items OUT Running Sum
20 10 10
0 5 5
30 8 27

if I input into the Sum's text box Control Source property
=Sum(nz([UnitsIn])-nz([UnitsOut])), I get the total of all records in each
Sum text box. I tried programming it in VBA but get the same. Any help...

Don
Click to expand...
Click to expand...
 
Back
Top