ID: Q143237
The information in this article applies to:
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.
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:
1. 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
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
'*************************************************************
'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
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.
for each currency. A Null value indicates that there were not enough
earlier values to compute that week's average.
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.
Keywords : kbprg MdlGnrl
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: November 21, 1998