Rolling Average

[blueshark]

Has any ever done a rolling average in Access or VBA? I didn't think it would be difficult, but here I am .

I'm trying to do an 8hr rolling average on valid data. It includes the data from this hour plus the previous seven. If a data point is invalid it is just exlcuded. Two data points can be thrown out of every 8 hr period for the rolling average to be considered valid. So I'll need to keep a count to mark those 8 hr averages < 6 entries as invalid.

Thanks

 

[Russell Hauf]

Difficult, yes. Impossible, no. Can you give a small example of your table? I'll need to know what the field that stores "what hour is what" looks like.

Thank you,

Russell

 

[Gates Is Antichrist]

Consider SELECT TOP ... should not be difficult to apply here.

You can convert your times to a purely chronological sequence - such as elapsed hours since 1/1/2000. TOP would then grab the 8 most recent, and you could work from there.

You may still have to contend with issues such as excluded items or invalid entries (e.g. "in-the-future") but it sounds like you've already thought ahead on those problems.

The >= 6 one could be tricky. Consider using IIF to give Null or "N/A" or something if the minimum of the top 6 is less than Now minus 8 hours, wrapped around your TOP 8 logic. I'll expand on this if you need (but ask anyone otherwise - I'm not tuned in frequently).

 

[blueshark]

The table is pretty simple.

Field:Data Type

Date:Date
Time:Text
Parameter:Text
Value:Double
Flag:Text

Date|Time|Parameter|Value|Flag
7/10/03|00:00|OX|33.3|
7/10/03|01:00|OX|38.4|
7/10/03|02:00|OX|93.3|<F
7/10/03|03:00|OX|44.6|
7/10/03|04:00|OX|39.0|
7/10/03|05:00|OX|35.5|
7/10/03|06:00|OX|33.4|
7/10/03|07:00|OX|33.9|
7/10/03|08:00|OX|33.7|
7/10/03|09:00|OX|34.6|
7/10/03|10:00|OX|44.1|
7/10/03|11:00|OX|42.2|>G
7/10/03|12:00|OX|33.3|
7/10/03|13:00|OX|33.1|
7/10/03|14:00|OX|13.3|
7/10/03|15:00|OX|0.03|<Z
7/10/03|16:00|OX|0.01|<Z
7/10/03|17:00|OX|0.01|<Z
7/10/03|18:00|OX|53.3|
7/10/03|19:00|OX|63.6|
7/10/03|20:00|OX|71.3|
7/10/03|21:00|OX|63.4|
7/10/03|22:00|OX|58.9|
7/10/03|23:00|OX|55.3|
7/11/03|00:00|OX|51.3|
7/11/03|00:00|OX|44.8|
7/11/03|00:00|OX|43.0|
7/11/03|00:00|OX|39.3|
7/11/03|00:00|OX|35.3|
7/11/03|00:00|OX|31.6|
7/11/03|00:00|OX|21.9|

 

[Russell Hauf]

Ok, first a couple of things:

1. You didn't specify what an "invalid" point was, so I didn't account for that.
2. I assumed that each hour has only one "point" (so I assumed that your table ended after: 7/11/03|00:00|OX|51.3|).
3. I made a new table using your table, but I combined the Date and Time fields into one field and dropped the other 2. Something like: fldDateTime: [Date]+[Time] (as an aside, I highly discourage naming fields Date and Time).

Ok, here's the SQL for our basic (not taking into account whether a point is valid or invalid) query. My table name is tbl071403a, and again I combined the date and time fields into fldDateTime:

• SELECT X.Parameter, X.fldDateTime, X.Value, (SELECT AVG(Value) FROM tbl071403a WHERE FldDateTime >=#7/11/2003#-TimeSerial(7,0,0) AND tbl071403a.fldDateTime<=X.fldDateTime) AS RollAvg
  FROM tbl071403a AS X
  WHERE (((X.fldDateTime)>=#7/11/2003#-TimeSerial(7,0,0)));

Change the table name (and field name(s) if necessary) to yours, then paste this into the SQL of a query (in the Query's Design View, go to View-SQL View) - then you can change back to Design View to see what was done. For the basics, see THIS post for info on how to create an alias (and therefore this query).

Hope it helps,

Russell

 

[Russell Hauf]

Sorry, in the SQL above I have 07/11/2003...this was because that was the end of your example data. You could replace this with Now(). So it would look something like:

• >=Now()-TimeSerial(7,0,0)

Sorry about that,

Russell