Query for Moving Average

[Basheuvel]

Hi,

I'm having issues with creating a correct Query for my MovingAverage module.

The module is as follows:

Sub MovingAverage()

Dim db As DAO.Database, rs As DAO.Recordset
Dim iVar1 As Double, iVar2 As Double, Dim iVar3 As Double, iVar4 As Double, iVar5 As Double

Set db = CurrentDb
Set rs = "SELECT 'DAX, DAXav' FROM 'Data' ORDER BY 'DATE';"

iVar1 = 0
iVar2 = 0
iVar3 = 0
iVar4 = 0
iVar5 = 0

While Not rs.EOF
  rs.Edit
  rs![DAXav] = (iVar1 + iVar2 + iVar3 + iVar4 + iVar5 + rs![DAX]) / 6
  rs.Update
  iVar1 = iVar2
  iVar2 = iVar3
  iVar3 = iVar4
  iVar4 = iVar5
  iVar5 = rs![DAX]
  rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

Which Query do I need to use to make this module work?

Thanks.

 

[Basheuvel]

The table I use contains the next info:

DATE DAX DAXav
1-1-1990 20300 Empty
2-1-1990 20308 Empty
3-1-1990 20350 Empty
4-1-1990 20340 Empty
5-1-1990 20360 Empty
6-1-1990 20323 Empty
7-1-1990 20330 Empty
8-1-1990 20350 Empty

 

[JANR]

To open a recordset on your Data table:

Set rs = db.OpenRecordset("Select DAX, DAXav From Data Order By [Date]", dbOpenDynaset)

Note that the DATE field is enclosed in square brackets because "Date" is a SQL reserved word

as a sidenote what you are attempting is storing a calculated value in a table and that is "normally" somthing that you shoulden't do since any change in the DAX field and your derived values in DAXav will be wrong and you have to re-run this sub every time a change can occure.

JR