pls look moving average VBA code

  • Thread starter Thread starter dewpaul
  • Start date Start date
D

dewpaul

Hello,

Please no SQL code (too slow) or pivot tables.

I want to calcuate a moving (running) average in Access using VBA. My data
consists of two columns. One column is TIME with every entry being 1 second
and the second is DATA (gas concentration with time) taken every 1 second.
I would like to filter the data using a moving average (say average every 60
seconds). In the end I would like filtered data to be exported as another
table/query with the two columns of filtered/averaged data.
I tried using an example from the Microsoft website and struggled without
success (http://support.microsoft.com/default.aspx?scid=kb;en-us;210138).
I can't figure out how the movelast, movefirst, movenext, and moveprevious
may work into things.

Questions: Is this code even a good start? How can I put the data back
into another table after filtering.
Thanks for all input!!

Function MovAvg(dataSource As Variant, period As Integer)
'dataSource is a column of gas concentration data
'period is the period to average over

Dim rs As DAO.Recordset
Dim sql As String
Dim n As Integer
Dim CPCdata As Variant

'the expression I use
'Expr1: MovAvg([CPC_3022],60)
'period = 5

'sql = "SELECT * FROM Table1 WHERE CPC_3022 = " & dataSource & ";"
Debug.Print "SELECT * FROM Table1 WHERE CPC_3022 = " & dataSource & ";"
sql = "SELECT * FROM Table1 WHERE CPC_3022 = " & dataSource & ";"

Set rs = CurrentDb.OpenRecordset(sql)
x = rs.BOF 'just a test
y = rs.EOF 'just a test
'RecordCount = rs.RecordCount 'for debugging
'MsgBox (RecordCount) 'for debugging
rs.MoveFirst
'rs.MoveLast
Debug.Print rs.RecordCount 'for debugging
'MsgBox (RecordCount)
'rs.RecordCount
For n = 0 To period - 1
If rs.EOF Then
MovAvg = 0
Exit Function
Else
ma = ma + rs.Fields("CPC_3022")
End If
'rs.MovePrevious
rs.MoveNext
Next n
rs.Close
MovAvg = ma / period

End Function
 
You have not calculated a moving average: you have calculate
just one Average value. How do you plan do a MOVING average?

to insert a value into a dao recordset column:

rs.edit
rs.fields("fieldname") = newvalue
rs.update


(david)
 
dewpaul said:
Please no SQL code (too slow) or pivot tables.
....
I
can't figure out how the movelast, movefirst, movenext, and
moveprevious may work into things.
Click to expand...

Tee hee: you think that trundling up and down a recordset will be quicker
than SQL..?

Tim F
 
Back
Top