moving (running) average

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

dewpaul

Hello,

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).
With the method below I get an error attibutable to "rst.MoveLast".

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 Long, period As Integer)
'dataSource is a column of gas concentration data
'period is the period to average over

Dim rst As DAO.Recordset
Dim sql As String
Dim n As Integer
Dim CPCdata As Long

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

sql = "SELECT * FROM (formattedLog) WHERE CPC_3022 = " & dataSource & ""

Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
MovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("CPC_3022")
End If
rst.MovePrevious
Next n
rst.Close
MovAvg = ma / period

End Function
 
Thanks for the code. I tried it and it will unfortunately be too slow for
the number of entries I have. In addition, I need the new table to give
the times in 60s intervals. Do you have a faster suggestion? Thanks again.

Brian Camire said:
Another (probably less efficient but easier to code) way of calculating a
moving average would be to use a query whose SQL looks something like this:

SELECT
[Your Table].[TIME],
(SELECT
Avg([DATA])
FROM
[Your Table] AS [Self]
WHERE
[Self].[TIME]<=[Your Table].[TIME]
AND
[Self].[TIME]>DateAdd("s",-60,[Your Table].[TIME])) AS [Moving Average]
FROM [Your Table];


This assumes that the "TIME" field has the Date/Time data type.

Unlike the the VBA-based example:

1. This approach will return an average for each record, even if there are
less than 60 data points. You could adjust the query to change this.

2. This approach does not assume that the "TIME" values are evenly spaced
with no gaps.

dewpaul said:
Hello,

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
Click to expand...
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).
With the method below I get an error attibutable to "rst.MoveLast".

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 Long, period As Integer)
'dataSource is a column of gas concentration data
'period is the period to average over

Dim rst As DAO.Recordset
Dim sql As String
Dim n As Integer
Dim CPCdata As Long

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

sql = "SELECT * FROM (formattedLog) WHERE CPC_3022 = " & dataSource & ""

Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
MovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("CPC_3022")
End If
rst.MovePrevious
Next n
rst.Close
MovAvg = ma / period

End Function
Click to expand...
Click to expand...
 
Back
Top