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

• Microsoft Access versions 2.0, 7.0, 97

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:

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




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




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, 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 




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



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

Additional query words:

Keywords : kbprg MdlGnrl
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto