ACC2000: How to Compute Moving Averages in Visual Basic for Applications (210138)

MORE INFORMATION

1. Start Microsoft Access and then open a new blank database. Create the following table, and then save it as 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. Open the table in Datasheet view, and then type the following values:

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

3. Open a new module, and then type the following functions:NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
   
   

   '*************************************************************
   'Declarations section of the module.
   '*************************************************************
   
   Option Explicit
   
   '===============================================================
   ' The following function MovAvg computes moving averages based on
   ' a table with a multiple-field primary key.
   '===============================================================
   
   
   Function MovAvg(currencyType, startDate, period As Integer)
     
       Dim rst As DAO.Recordset
       Dim sql As String
       Dim ma As Currency
       
       sql = "Select * from table1 "
       sql = sql & "where currencyType = '" & currencyType & "'"
       sql = sql & " and transactiondate <= #" & startDate & "#"
       sql = sql & " order by transactiondate"
       
       Set rst = CurrentDb.OpenRecordset(sql)
       rst.MoveLast
       For n = 0 To period - 1
           If rst.BOF Then
               MovAvg = 0
               Exit Function
           Else
               ma = ma + rst.Fields("rate")
           End If
           rst.MovePrevious
       Next n
       rst.Close
       MovAvg = ma / period
   
      
         
   End Function
   					

4. Create the following query that is based on the Table1 table, and then save it as CalcAverage:

   Field: CurrencyType
   Field: TransactionDate
   Field: Rate
   Field: Expr1: MovAvg([CurrencyType],[TransactionDate],3)

   NOTE: This query generates a three-week moving average of the Rate data. To compute a longer or shorter moving average, you can change the number 3 in the Expr1 column of the query to the value that you want to compute.
5. Run the query.
   
   Note that you see the following three-week moving average for each currency. A Null or Zero value indicates that there were not enough earlier values to compute the average of that week.

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

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