Running average of 3 previous Records

[AndrewCorkery]

Hi,

Just wondering if someone knows how to do the following:

I have a field that contains numerical data daily data and I need to get a average of this data for a 3 day period.

For eg:

DailyPrice RollingAverage
3
4
5 4
6 5
7 6

Any ideas would be much appreciated!

Thanks...

 

[PaulBricker]

This is modified from something else I did for someone. Basically it runs a delete query "deletetblTemp" that clears out tblTemp. tblTemp is a table that stores your info in a way that you can average it. It has three fields. myValue, myValueA and myValueB. qrydata is a query of the data you want to write to tblTemp. Then after it's in tblTemp, you can put it in a query to add the three values and find the average. Post back with any problems or questions.

Paul

Function fNextValues()

DoCmd.SetWarnings False
DoCmd.OpenQuery "deletetblTemp"
DoCmd.SetWarnings True
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Set rst2 = CurrentDb.OpenRecordset("tblTemp", dbOpenDynaset)
Set rst = CurrentDb.OpenRecordset("qrydata", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF

rst.MoveNext
rst.MoveNext



Do Until rst.EOF

rst2.AddNew

rst2!myValue = rst!FieldNameHere
rs.MovePrevious
rst2!myValueA = rst!FieldNameHere
rs.MovePrevious
rst2!myValueB = rst!FieldNameHere

rst2.Update
rst.MoveNext
rst.MoveNext
rst.MoveNext
Loop
End If
rst.MoveNext
Loop




End Function

 

[SBendBuckeye]

Assuming you want the most recent 3 days and that there is one summary record per day then try the following:

Dim strSQL As String

strSQL = "SELECT AVG([DailySummary]) AS [3 Day Ave] " _
& "FROM [YourTableName] " _
& "WHERE [DailySummaryDate] IN " _
& " (SELECT TOP 3 [DailySummaryDate] " _
& " FROM [YourTableName] " _
& " ORDER BY [DailySummaryDate] DESC);"

This code selects the last 3 days summary table dates by using a subselect and then passes those 3 dates up to the main query.

If you don't have any embedded spaces in your field names or table names you don't need the square brackets [].

Since you are not referencing the main query from inside the subquery, you don't need to alias anything although you can if it helps to keep the code straight.

If you want the first 3 days then remove the DESC from the subquery ORDER BY clause. If neither of these helps you, feel free to repost and we'll try to help you.

Good Luck!