Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Compute Moving Averages in Visual Basic for Applications

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

View products that this article applies to.

Summary

This article describes 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 specified 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 that is based on the current value and the values from the two previous weeks.

↑ Back to the top

More information

The following sample function computes moving averages that are 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. 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
    Dim n As Integer
    
    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.

↑ Back to the top

Keywords: KB210138, kbprogramming, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 210138
Revision : 7
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 511