Running Sum

[Jon K]

In the attached new DB, I have added two fields for the scores and a DailyTotal field.

The code is now in the After Update event of the text box Score3.

 

[vjmehra]

Hello again, thanks for all your help so far, but the further on I get with this DB the more I realise I can't do! So far the code you have given me is working fine, however I had actually intended to use this for a slightly more complex process which I thought I'd be able to work out, but I can't get my head round it.

1) Ok basically the first thing I have been trying to do is get a running count of all the scores entered (i.e. only those above 0), I thought I would be able to modify your code slightly to do this by changing the dtotal to a dcount, but I wasn't quite sure how to go about this, so I thought I'd leave it to someone who actually knows what they are doing.

2) I then want to create an average, which is simple enough, that will just be Running/Count.

3) This next bit may be a bit tricky, basically I then want to create another field which is calculating a handicap score (this is calculated as 0.67*(200-Average), however I want this handicap to appear for the following record, i.e. if scores are entered on the 02/02/03 and the next record is dated 02/03/04 I want the handicap from the first record to appear for the second. Looking at your code it may be possible to use your dmax statement to somehow pull this out, I'm going to carry on playing around with it, but I am unlikely to get very far so if you get a chance to help me out that would be great.

Oh one more thing, which is actually probably the most important part, and the part I am having the most trouble with. Basically each record is assigned to a different person, based on a number (eg. John is 1, Mike is 2 etc.), I need the running sum (and all the other parts to apply to each person rather than the DB). I have attached a sample DB to show you what I have so far.

Cheers,
Vidal

 

[Jon K]

The code for calculating Count, Average and Handicap has been added in the DB.

 

[Jon K]

>>
.... get a running count of all the scores entered (i.e. only those above 0)
<<


On re-reading your post, I find that I have overlooked the part about counting only those scores that are above 0 and have counted the number of records instead.

Here is a revised DB, in which the figure of Count represents the number of scores that are above 0.

 

[vjmehra]

Thanks again, there is just one small thing I was wondering if you could help me with. Is it possible to make the 'Handicap' Value 0 for the first record, as I want to be able to display HSeries for every record, which is fine apart from Hseries comes up as 0 for record 1, also as an example if the score in game 2 is 0 would it be possible to have h2 displayed as 0?

 

[Jon K]

I have added HCap=0 in the code to initialise Handicap as zero, and have used an IIF expression in the Control Source of H1, H2, and H3 to return 0 when Handicap is 0 (i.e. the first record) or if Game1, Game2 or Game3 is 0.

 

[vjmehra]

Once again, thanks for your help, just one question, I tried to run the following if statement =IIf("([H1]+[h2]+[h3])>([game1]+[game2]+[game3])",[h1]+[h2]+[h3],[series])

but it always returns the first argument, even though for the first record it shouldn't. Any idea why this might be happening?

Cheers,
Vidal

 

[Jon K]

Remove the double quotes:-

=IIf([H1]+[h2]+[h3]>[game1]+[game2]+[game3],[h1]+[h2]+[h3],[series])

 

[vjmehra]

Jon, I copied your formulae straight in and I keep getting a 'Name' error, I've checked the names of all the controls and they all seem to be okay, so I'm a bit confused now. Do you know why this could be?

 

[Cosmos75]

Cosmos, there is a difference between a running sum and a balance. Most people only NEED a balance but they WANT to see a running sum. You can always create a running sum in a temp table with code if you need to for a graph or something. You all work on such small databases that you never see what happens once your table starts to grow. Try doing a running sum in a query on a table with 10,000 rows. That's not even large. Oh, and make sure your back end db is on the network.

me feel so small now...

 

[Cosmos75]

Pat Hartman said:

there is a difference between a running sum and a balance.

Pat,

What do you mean by balance here?

Is there a difference between using JonK's query and using code to create a Running Sum? Would the code you to make the temp table be using SQL or DAO recordset?

 

[Jon K]

vjmehra, when I copied the formula to the Control Source of a new text box, it ran fine in my database. I have no idea why an error should pop up.


>
Jon, if I ask nicely will you build my next app for me
>

Pat, you are my Access mentor. I have learnt so much from reading your posts. My solutions are usually superficial. Yours always show much deeper insight and deal with the issues at the very core.

 

[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.