ACC1x: How To Compute Moving Averages in Access Basic (1.x)

ID: Q106245

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

The following sample Access Basic function computes a moving average for a set of values in a table based on a given period of time. For example, if you have data that has been collected weekly for the past year, and you want to compute a historical average for a three-week time period on each value, you can use this function to return an average for each value in the table based on the current value and the values from the two previous weeks.

MORE INFORMATION

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x.

The sample function below computes moving averages based on a table with a multiple-field primary key. The weekly values of foreign currencies are used for this example.

Create the following table. Save the table as Table 1.


      Table: Table 1
      -----------------------------------------
      Field Name: CurrencyType [Primary Key]
      Data Type: Text
      Field Size: 25

      Field Name: TransactionDate [Primary Key]
      Data Type: Date/Time
      Format: Short Date

      Field Name: Rate
      Data Type: Currency
      Decimal Places: 4

View the table in Datasheet view, and type the following values:


      CurrencyType     TransactionDate     Rate
      --------------------------------------------
      Yen              8/6/93              $0.0079
      Yen              8/13/93             $0.0082
      Yen              8/20/93             $0.0085
      Yen              8/27/93             $0.0088
      Yen              9/3/93              $0.0091
      Mark             8/6/93              $0.5600
      Mark             8/13/93             $0.5700
      Mark             8/20/93             $0.5800
      Mark             8/27/93             $0.5900
      Mark             9/3/93              $0.6000

Open a new module and type the following functions:


      '*************************************************************
      'Declarations section of the module.
      '*************************************************************

      Option Explicit

      '===============================================================
      ' The following function MAvgs computes moving averages based on
      ' a table with a multiple field primary key.
      '===============================================================


      Function MAvgs(Periods As Integer, StartDate, TypeName)
         Dim MyDB As DATABASE, MyTable As TABLE, MySum As Double
         Dim i, x
         Set MyDB = CurrentDb()
         Set MyTable = MyDB.OpenTable("Table 1")
         On Error Resume Next
         MyTable.Index = "PrimaryKey"
         x = Periods - 1
         ReDim Store(x)
         MySum = 0
         For i = 0 To x
           MyTable.MoveFirst
           MyTable.Seek "=", TypeName, StartDate
                ' These two variables should be in the same order as the
                ' primary key fields in your table.
            Store(i) = MyTable![Rate]
            If i <> x Then StartDate = StartDate - 7
                '7 here assumes weekly data; you would use 1 for daily
                '  data.
            If StartDate < #8/6/93# Then MAvgs = Null: Exit Function
                '#8/6/93# is replaced with the earliest date of the data
                '  in your table.
            MySum = Store(i) + MySum
            Next i
         MAvgs = MySum / Periods
         MyTable.Close
      End Function

Create the following query based on Table 1:
```
      Query: Query1
      ------------------------------------------------------
      Field: CurrencyType
      Field: TransactionDate
      Field: Rate
      Field: Expr1: MAvgs(3,[TransactionDate],[CurrencyType]) 
```
NOTE: This query will generate a 3-week moving average of the Rate data. To compute a longer or shorter moving average, change the "3" in the query's Expr1 column to the value you want to compute.

Run the query.

You will see a 3-week moving average for each currency. A null indicates that there were not enough prior values to compute that week's average.

NOTE: If you want to compute a moving average for a table with a single primary key, use the primary key both as an argument to be passed to the function and as the key value for the Seek method.

Additional query words: rolling

Keywords : kbprg
Version : WINDOWS:1.0,1.1
Platform : WINDOWS
Issue type : kbhowto