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

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

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

2. 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
   
   

3. 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
   
   

4. 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.

5. Run the query.

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.