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

ID: Q143237


The information in this article applies to:
  • 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


Last Reviewed: October 13, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.