Using a Do loop to calculate Average

[pmpursley]

Hello,



I'm new to Access and programming in general. I'm trying to understand loops but I'm not making any progress. I'm trying to write a loop to calculate the average of the past 10 Values including the current Value. This is what I have so far.
There are also Fields for "Date" and "Time" if that is useful.

[code]
Dim index As Integer
Dim RollAveTotal As Long
Dim CurrentValue As Variant

Do
'There are 4 possible Grades. I only need "Grade 1"
If Me.Grade = "Grade 1" Then
'Status will be either "Y" or "N". Only Records with "Y" are to be counted. "N"s should be ignored. The "Current
If Me.Status = "Y" Then
RollAveTotal = RollAveTotal + Me.Value
CurrentValue = Me.Value
RollingAverage = (RollAveTotal + Value) / 10
End If
End If
index = index + 1
Loop Until index = 9
[\code]

Thanks for any help or advice!

[ItsMe]

Have you tried building and saving a query object? The query builder can help you create a totals query. You can use the average function in the totals query.

[June7]

That code won't work without a recordset.

Like ItsMe said, build an aggregate GROUP BY (Totals) query to average records. The trick will be limiting to most recent 10 records. The TOP N qualifier might serve. Review: http://allenbrowne.com/subquery-01.html#TopN

Or build a report with the TOP N query as RecordSource and use the report Grouping & Sorting with aggregate calcs features. This will allow display of detail as well as summary data.