Running Sum

[vjmehra]

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]

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]

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.

 

[vjmehra]

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]

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]

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?

 

[Rich]

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]

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

Cheers

 

[Rich]

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]

Column 1 is an autonumber

 

[Jon K]

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.

 

[Jon K]

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.

 

[vjmehra]

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]

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]

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.

 

[Cosmos75]

JonK,

WOW! You've really taken the running sum issue and ran with it!

(Sorry, couldn't resist!)

 

[Rich]

One "James Romborough" told me many years ago a stored running sum has no place in a relational db. Was he wrong?

 

[Cosmos75]

Pat & Rich,

Why is it that a stored running sum has no place in a database?

- at least with Access, I find times when I would like to be able to create a Chart based on a Running Total.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31806&highlight=Running

On top of the running sum, I needed to be able to check how that running total was doing against a budget. So say for four weeks, the budget was $4000. I could check the running sum against a running budget, where the running budget would be for week one = 1 * (4000/4), week 2 = 2 * (4000/4) and so on.

Also I actually took JonK's method and did an Running Count to create a Running Avg. [Which I think is cool!]

 

[Jon K]

Sometimes we may need to denormalize a database.

In the book "Access 97 Developer's Handbook" Third Edition (SYBEX), the authors, when teaching Database Design, laid down some guidelines on breaking the rules of normalization.

<Quote>
Breaking the Rules: When to Denormalize

Sometimes it's necessary to break the rules of normalization and create a database that is deliberately less normal than Third Normal Form. You'll usually do this for performance reasons or because the users of the database demand it. While this won't get you any points with database design purists, ultimately you have to deliver a solution that satisfies your users. If you do decide to break the rules and denormalize your database, however, it's important that you follow these guidelines:

- Break the rules deliberately; have a good reason for denormalizing.
- Be fully aware of the trade-offs this decision entails.
- Thoroughly document your decision.
- Create the necessary application adjustments to avoid anomalies.

[The authors gave two scenarios:]
- You decide to store an indexed computed column, Soundex, in tblCustomer to improve query performance, in violation of 3NF (because Soundex is dependent on LastName).

- To improve report performance, you decide to create a column named TotalOrderCost that contains a sum of the cost of each order item in tblOrder. This violates 2NF because TotalOrderCost is not dependent on the primary key of the table. ...... Since you often create reports that need to include the total order cost but not the cost of individual items, you break 2NF to avoid having to join these two tables every time this report needs to be generated.
<End quote>

The second scenario demonstrated the need of storing a calculated field in a table, though in violation of relational database design. I wonder what "James Romborough" would say about this.

 

[vjmehra]

Is it possible to use this function for three columns (e.g. score 1, score 2 and score 3), which is what I actually have, I just assumed it would be easy enough to modify your code myself so I didn't mention it, however I discovered I couldn't, so if you feel like helping me out again that would be great.