average of 2 numbers

[ersatz]

Average of two numbers
Hi,
I have a column in my access database table and I want to add another column with average of every two numbers, like that:
Field1 field2
A1 “”
A2 (a2+a1)/2
A3 (a3+a2)/2
A4 (a4+a3)/2
An (an+a(n-1))/2
Can everybody help me, please?
Regards,
ersatz

 

[MasterPO]

The only way I could think of to do this would be to run a function that would read the records sequentially and update the 'average' field as it went along. The following function relies on an Autonumber ID field to be sure the records are read in the correct sequence.

Hope this helps!

Function UpdateAvg()

Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim RecNum As Long
strSQL = "Select * from tblValues ORDER BY ID"

rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

CurrValue = 0
PrevValue = 0
RecNum = 1
'-- Get the value for record num 1, but don't try to average it
PrevValue = rst("Field1")
rst.MoveNext

Do While Not rst.EOF
    CurrValue = rst("Field1")
    Average = (PrevValue + CurrValue) / 2
    PrevValue = CurrValue
  
    rst("Field2") = Average
    rst.Update
    rst.MoveNext
Loop
rst.Close
MsgBox "Done!"
End Function

 

[ersatz]

Thanks
It works great.
but if I want to calculate for 20 values
like that:
(a1+a2+ ....+a20)/20
(a2+a3+.....+a21)/20
and the first 19 lines must be null??
thanks again,
ersatz