Microsoft KB Archive/143237

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

Article ID: 143237

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q143237





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:

  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   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   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   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.   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. <pre class="fixed_text">     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. </li></ol>

Keywords: kbhowto kbprogramming KB143237

-

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

© Microsoft Corporation. All rights reserved.