Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    7

    Unanswered: Adding a running average to my report

    Hi all,

    I currently have a database that records my tenpin bowling scores. A while ago I got some help to add various statistics to it, but I am now looking at adding a running average to my report.

    Attached is an image of the report in question. You will see that 3rd column in from the left is average. This is made up of the total of the 3 games / 3. Easy!

    However, what I would like to do is a running average. So, the first week the running average would obviously be 185. The next week it would be 177.6 (the 6 games added together / 6, or the 2 averages added together / 2)
    The following weeks would be 183.66 and then 194.58.

    I have been trying to do this for ages but am failing miserably. Can anyone help please?

    Many thanks

    Matt
    Attached Thumbnails Attached Thumbnails bowling_report.gif  

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Matt

    The attached database may help you solve your problem.
    The table tbl_score contains three fields to hold your weekly game scores.
    After entering the weekly scores you will need to run the module mod_calc_prog_avg by putting this line of code calc_prog_average after you save the the weekly scores.

    The module stores the running totals for each game for all the rounds then determines the average for all games and stores the average in the last record.
    Attached Files Attached Files

  3. #3
    Join Date
    Jun 2008
    Posts
    7
    Thank you very much for your prompt reply. I have had a go at putting in the module into my database. Unfortunately I keep getting a compile error (and to be honest, I have no idea what that means. Sorry)

    I have attached a live version of my database with the module in it, and the code changed to 'hopefully' reflect the correct references in the module.

    I wondered if you'd mind having a look at this for me and seeing where I am going wrong?

    Cheers and thanks again!

    Matt
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2008
    Posts
    7
    Sorted it. I needed to add the DAO to the reference library in the module area

    Working a dream now, although I can't get it to go backwards but never mind

    Thank you for all your help

    Cheers

    Matt

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Matty

    I could not open your zip file.

    although I can't get it to go backwards
    What do you mean by this statement?

    If you run the following code prior to opening your report the averages will be updated in the table.

    Suppose you found an error in the score for an earlier round then by running this code the running averages will be updated.

    Code changes to my previous example are shown in bold.


    Sub calc_prog_average()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Dim game_1 As Integer
    Dim game_2 As Integer
    Dim game_3 As Integer
    Dim record_counter As Integer
    Dim round_avg As Double

    Set db = CurrentDb
    ' open the table in match date order
    Set rst = db.OpenRecordset("SELECT tbl_score.* FROM tbl_score ORDER BY tbl_score.match_date")


    game_1 = 0
    game_2 = 0
    game_3 = 0

    With rst

    .MoveFirst
    record_counter = 0
    Do Until .EOF
    record_counter = record_counter + 1

    ' store the progressive total for each game
    game_1 = game_1 + ![game_1]
    game_2 = game_2 + ![game_2]
    game_3 = game_3 + ![game_3]

    'calculate progressive average for the last round
    round_avg = (game_1 + game_2 + game_3) / (record_counter * 3)

    'update current record with the running average
    rst.Edit
    rst("round_avg") = Format(round_avg, "0.00")
    rst.Update

    .MoveNext

    Loop


    End With

    End Sub

  6. #6
    Join Date
    Jun 2008
    Posts
    7

    Thank you

    Hi,

    Sorry for late reply, but was away this weekend. Many thanks for all your help, all working fine now. Much appreciated

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •