ACC: How to Compute Moving Averages in Visual or Access Basic (143237)

SUMMARY

This article shows you how to create a sample Visual Basic for Applications function to compute 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.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

The following sample function computes moving averages based on a table with a multiple-field primary key. The weekly values of foreign currencies are used for this example. To create the sample function, follow these steps:

Create the following table and save it as Table1:

      Table: Table1
      -----------------------------------------
      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 enter 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, MyRST As Recordset, MySum As Double
         Dim i, x
         Set MyDB = CurrentDb()
         Set MyRST = MyDB.OpenRecordset("Table1")

         On Error Resume Next

         MyRST.Index = "PrimaryKey"
         x = Periods - 1
         ReDim Store(x)
         MySum = 0

         For i = 0 To x
            MyRST.MoveFirst
            MyRST.Seek "=", TypeName, StartDate

            ' These two variables should be in the same order as the
            ' primary key fields in your table.
            Store(i) = MyRST![Rate]

            If i <> x Then StartDate = StartDate - 7
            ' The 7 here assumes weekly data; 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
         MyRST.Close
      End Function

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

Run the query. Note that you see the following three-week moving average for each currency. A Null value indicates that there were not enough earlier values to compute that week's average.

      CurrencyType     TransactionDate     Rate      Expr1
          Mark            08/06/93        $0.5600
          Mark            08/13/93        $0.5700
          Mark            08/20/93        $0.5800    0.57
          Mark            08/27/93        $0.5900    0.58
          Mark            09/03/93        $0.6000    0.59
          Yen             08/06/93        $0.0079
          Yen             08/13/93        $0.0082
          Yen             08/20/93        $0.0085    0.0082
          Yen             08/27/93        $0.0088    0.0085
          Yen             09/03/93        $0.0091    0.0088

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.