Running Sum

[vjmehra]

If ound out why it wasn't working, I have 2 forms, one is the data entry form and the other is based on a parameter query to get the scores up to a certain date. I was trying to use the formulae in the wrong form, it works fine now. I've bought myself a book on VBA now so I can actually try and learn a bit myself and stop bugging you!! Thanks a lot for your help though.

 

[Cosmos75]

Pat Hartman said;

To give you some idea of the magnitude of this, a running sum calculated in code and written either to a table/file/report needs to read 100 records and write 100 records. A running sum calculated in a query for the same 100 records, needs to read 4950 records and update 100 records or if you use the technique in the sample db that this post has been using, those 100 records are updated 100 * 100 (every record is updated every time) times or 10,000 times and the 4950 records are read 4950 * 100 times or 495,000 reads all together.

Yikes!


Epiphany: I must really learn how to use ADO or DOA records sets in VBA someday!

Any good books for a beginner? I have rudamentary (a.k.a child's play for the gurus here) VBA in ACCESS, stuff like

If me.Field >=10 then
me.Field2 = 5
me.Field3.SetFocus
me.cboCombo.requery
me.Form1.RecordsSource = "qry1"
End if

sigh...

 

[mr moe]

Pat do you know how to do this in a table. Most likely this should be a modeul, which has to run once, because the other method can cause a problem if later on you delete a record. Please let me know. Again how do you do this in a table.

 

[Pat Hartman]

Pat do you know how to do this in a table

- yes.

Do I have any sample code to post? No. I don't store calculated values so I haven't had the occasion to write this particular function. The code is extremely simple if you know any programming language. Define a variable to hold the running sum. Loop through the table once. Inside the loop add the current record value to the variable that is accumulating the running sum and update the table field with the value in the variable.