# Thread: Rolling Average (Access 2000)

1. ## 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. ## 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

3. ## 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. ## Re: Rolling Average (Access 2000)

Hans

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

5. ## 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,

6. ## 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
•