Microsoft KB Archive/210138

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

Article ID: 210138

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210138



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



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.



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

  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   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  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.  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. </li></ol>

Keywords: kbhowto kbinfo kbprogramming KB210138

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.