Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rolling Average (Access 2000)

    Is it possible to calculate rolling averages in a query?
    I have production information summarised by week number and would like to calculate a rolling average of the last 8 weeks for each week number.

    Any help is greatly appreciated.
    Graham

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rolling Average (Access 2000)

    I don't know what a "rolling" average is but have you look at the DAvg function in the help file.
    HTH
    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rolling Average (Access 2000)

    Francois' suggestion is one possibility. Another one is described in ACC2000: How to Compute Moving Averages in Visual Basic for Applications.

  4. #4
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rolling Average (Access 2000)

    Hans

    Thanks for pointing me in the right direction. That's just what I needed.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rolling Average (Access 2000)

    Hans,

    Here's me still looking for answers. An average cost was the phrase that was originally given to me, but today they decided to call it a rolling/moving average so I did an immediate search and came up with your response to Graham. The good news is I found the MS site, entered data as suggested and their module code:
    ************************************************** ***********
    'Declarations section of the module.
    '************************************************* ************
    Option Explicit
    '================================================= ==============
    ' The following function MovAvg computes moving averages based on
    ' a table with a multiple-field primary key.
    '================================================= ==============
    Function MovAvg(currencyType, startDate, period As Integer)
    Dim rst As DAO.Recordset
    Dim sql As String
    Dim ma As Currency
    sql = "Select * from table1 "
    sql = sql & "where currencyType = '" & currencyType & "'"
    sql = sql & " and transactiondate <= #" & startDate & "#"
    sql = sql & " order by transactiondate"
    Set rst = CurrentDb.OpenRecordset(sql)
    rst.MoveLast
    For n = 0 To period - 1
    If rst.BOF Then
    MovAvg = 0
    Exit Function
    Else
    ma = ma + rst.Fields("rate")
    End If
    rst.MovePrevious
    Next n
    rst.Close
    MovAvg = ma / period
    End Function

    and lo and bloody behold there was a compilation error in their code with the line:

    For n = 0 To period - 1

    being highlighted.

    Who would have thunk that the MS boys would have coding errors. Or is it me. Again. Still.

    I was just curious as to whether or not you had tried it and what success you had. I guess this question can also be aimed at Graham. I'll be interested in your comments,
    Cheers,
    Andy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rolling Average (Access 2000)

    The variable n hasn't been declared. Add the following to the declarations at the beginning of the function:

    Dim n As Integer

Posting Permissions

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