Running Sum (1 Viewer)

vjmehra · Apr 5, 2003

I am trying to create a running sum so that I can total up a field after every entry. The table is in the format below:

Column 1- ID
Column 2- Date
Column 3- Score

The ID is unique for each record, however the date is not. I have created a running sum which works, except I want the totals to be sorted by date, not ID as at the moment I am getting odd results such as the current total on 03/03/03 is lower than on 05/04/02 becuase I entered the record before.

Any ideas?

Jon K · Apr 5, 2003

Type this in the Field: cell of a column in the query grid (using the correct table name):-

RunningSum: (Select Sum([Score]) from [TableName] as a where a.[Date]<=[TableName].[Date])

And set the Sort order of the Date field to Ascending.

(Note: The query works only if there are no duplicate dates in the table. If there are duplicate dates, you will need to use VBA to loop through the records.)

vjmehra · Apr 5, 2003

Thanks for that, but there are duplicate dates so how would I go about looping through the records like you suggested, I have never used VBA before so I have no idea.

Pat Hartman · Apr 5, 2003

You should be able to do the running sum in a report without any problem.

vjmehra · Apr 5, 2003

That is true, but I wanted it in a form, I can't remember why now, but I remember thinking it had to be in a form rather than a report.

Rich · Apr 6, 2003

The following code will allow you to create a running sum on a form

Function RunSum(F As Form, KeyName As String, KeyValue, _
FieldToSum As String)
'***********************************************************

' FUNCTION: RunSum()
' PURPOSE: Compute a running sum on a form.
' PARAMETERS:
' F - The form containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.

' EXAMPLE: =RunSum(Form,"ID",[ID],"Amount")
'***********************************************************
Dim rs As Recordset
Dim result

On Error GoTo Err_RunSum

' Get the form Recordset.
Set rs = F.RecordsetClone

' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE

rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunSum
End Select

' Compute the running sum.
Do Until rs.BOF
result = result + rs(FieldToSum)

' Move to the previous record.
rs.MovePrevious
Loop

Bye_RunSum:
RunSum = result

Exit Function

Err_RunSum:
Resume Bye_RunSum

End Function

vjmehra · Apr 6, 2003

Although I'm sure all that code will do the job I have never used VBA and I don't really have a clue about how to even input this correctly, can I basically just copy your code and paste it somewhere else?

Pat Hartman · Apr 6, 2003

Even with Rich's code, creating a running sum on a form REQUIRES a unique ascending id which you don't have. A report processes the recordset sequentially only once and so doesn't have the problem of having non-unique values moving around. A report is also SUBSTANTIALLY more efficient. If you must end up with a recordset that contains the values because you need to use it in another application, you can write code in a report's format event to write each record to a table with a unique sequence number and the running sum.

I would use the report technique because of its efficiency even if I had a unique ID and so could create the running sum in a query.

Rich · Apr 6, 2003

Assuming you have a valid reason for doing this at form level the code will work even if the key field is out of sequential order, although you'll have to refresh the form regularly as you scroll and I would only use it on small recordsets

vjmehra · Apr 6, 2003

Sorry I still haven't worked out how to input the code, could you tell me where I need to put it.

Cheers

Pat Hartman · Apr 7, 2003

Rich, the problem is not that the records are out of order (although I don't believe the code will work in that case either), the problem is that the table has NO unique id so there is no way to accurately position the recordset with FindFirst. The code will always start at the first of the duplicate records (assuming the recordset is in order by the key field) and move backward from there.

If what you really want is a "balance", you can do that by adding a footer to the sub form and using a control that sums the records in the subform's recordset. No coding is necessary.

=Sum(SomeField)

Expand the size of the control on the main form so that the subform's footer will show.

Rich · Apr 7, 2003

I agree with you Pat that the theory would indicate the code could not work, fortunately for me it does, I've no idea why

I had assumed in this case that column 1 was an autonumber

vjmehra · Apr 7, 2003

Column 1 is an autonumber

Jon K · Apr 7, 2003

Attached is a sample DB. The form is based on the query qryScores. When a new record is entered or a score is edited, the running sum will be re-calculated.

The code used is contained in the After Update event of the text box Score on the form:-
---------------------------------
Private Sub Score_AfterUpdate()
' if date is empty, exit.
If IsNull(Me![Date]) Then
MsgBox "The date cannot be empty"
Me![Date].SetFocus
Exit Sub
End If

' save current record.
DoCmd.RunCommand acCmdSaveRecord

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String
Dim RSum

SQL = "Select [Score], [RunningSum] from [tblScores] order by [Date], [ID]"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

' loop through the records.
Do While Not rs.EOF
RSum = RSum + rs![Score]
rs.Edit
rs![RunningSum] = RSum
rs.Update
rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

' requery the form.
Me.Requery
End Sub
------------------------------------

As records will not be entered in the order of dates, the code has to re-calculate the whole running sum whenever a new record is added. When the table grows, it will take longer to calculate.

The DB was written in Access 97, so DAO was used. If you write the code in Access 2000 or 2002, you must make a reference to DAO (when the code window is open, choose menu Tools, References... select the Microsoft DAO 3.6 Object Library.)

Note: If you use Access 2000 or 2002, choose Convert and save as a new name when the attached DB is opened for the first time.

Edit
The DB has been replaced by an improved version in the post below.

Pat Hartman · Apr 7, 2003

The sample works but keep in mind folks that it updates EVERY record in the table whenever a new record is added. Do you REALLY want to do this for tables containing thousands of rows? I also suspect that there would be trouble with this method in a multi-user environment since you are certain to run into contention if two people are adding a row at the same time.

I will stick to a current balance for my forms and leave the running sum to reports where it can be calculated efficiently.

Jon K · Apr 8, 2003

Pat's comment inspired me to improve the code in the sample DB.

Instead of having to re-calculate the running sum for every record, now only those records that need to be re-calculated are calculated.

--------------------------------
Private Sub Score_AfterUpdate()
' if date is empty, display message and exit.
If IsNull(Me![Date]) Then
MsgBox "The date cannot be empty"
Me![Date].SetFocus
Exit Sub
End If

' save current record.
DoCmd.RunCommand acCmdSaveRecord

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String
Dim RSum
Dim CurRecord As Long

' note current record position.
CurRecord = Me!ID

SQL = "Select [Score], [RunningSum] from [tblScores]" & _
" where [Date] >= #" & Me![Date] & "#" & _
"order by [Date], [ID]"

' Running sum of previous record.
RSum = Nz(DMax("RunningSum", "tblScores", "[Date]< #" & Me![Date] & "#"))

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

' loop through the records.
Do While Not rs.EOF
RSum = RSum + rs![Score]
rs.Edit
rs![RunningSum] = RSum
rs.Update
rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

' requery the form.
Me.Requery

' set focus to current record position.
Me.RecordsetClone.FindFirst "[ID]=" & CurRecord
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub
--------------------------------

The revised version of the DB is attached.

Pat Hartman · Apr 8, 2003

I love it when I inspire people. You've spent an amazing amount of effort doing something that violates relational database design (which is why there is no aggregate function that will perform the calculation) but at least you have done it well.

vjmehra · Apr 9, 2003

Thanks for that, it seems great except......... when you enter a date that is older than a date already in the list the running sum treats it as the last record (so I assume it must be looking at the ID at this point rather than the date).

Jon K · Apr 9, 2003

When I entered two records in this order:-

Date ------- Score
3/31/2003 -- 10.4
3/15/2003 -- 20.8

and contrary to your statement that ".... when you enter a date that is older than a date already in the list the running sum treats it as the last record",

I got this result on the form (as shown in the jpg attached):-

ID -- Date ------- Score ----- RunningSum
2 --- 3/15/2003 -- 20.8 ------ 20.8
1 --- 3/31/2003 -- 10.4 ------ 31.2

I thought it was in the format that you wanted, though I might be wrong. But if it is not what you wanted, then how would you like it to show?

vjmehra · Apr 9, 2003

Yes I've just tried it again and you are right, it seems to be working fine, but when I tried it before it didn't seem to update for some reason, maybe I forgot to move onto the next record or something stupid like that . Thanks for this, there is no way I'd would have been able to crack this one on my own, this is much appreciated.

Running Sum (1 Viewer)

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Rich

Guest

Registered User.

Super Moderator

Rich

Guest

Registered User.

Super Moderator

Rich

Guest

Registered User.

Registered User.

Super Moderator

Registered User.

Attachments

Super Moderator

Registered User.

Registered User.

Attachments

Registered User.

Similar threads

Users who are viewing this thread