Microsoft KB Archive/106245
ACC1x: How To Compute Moving Averages in Access Basic (1.x)
The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
Advanced: Requires expert coding, interoperability, and multiuser skills.
The following sample Access Basic function computes 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 Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x.
The sample function below computes moving averages based on a table with a multiple-field primary key. The weekly values of foreign currencies are used for this example.
- Create the following table. Save the table as Table 1.
- View the table in Datasheet view, and type the following values:
- Open a new module and type the following functions:
Create the following query based on Table 1:
Query: Query1 ------------------------------------------------------ Field: CurrencyType Field: TransactionDate Field: Rate Field: Expr1: MAvgs(3,[TransactionDate],[CurrencyType])
NOTE: This query will generate a 3-week moving average of the Rate data. To compute a longer or shorter moving average, change the "3" in the query's Expr1 column to the value you want to compute.
- Run the query.
You will see a 3-week moving average for each currency. A null indicates that there were not enough prior values to compute that week's average.
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: rolling
Keywords : kbprg
Issue type : kbhowto
Last Reviewed: November 4, 2000