Rolling Average

[bejammin]

Is there any way i can calculate a rolling average for a field in a record, based on the 10 previous records?

Cheers,
Ben

 

[dtigereye]

Did you ever where able to solve this? I have the same problem.. looking for a VBA function to do this on a report.

 

[Galaxiom]

A function is not necessary. It can be done with a self join in an aggregate query.

The join is used to connect the previous ten records in the alias table to each record in the main table. This must be done in the SQL view using the values from the field which would define the "previous" records.

Group By on the ID field in the main table and average the value from the field in the alias.

Then any other required fields can be connected back to the results by joining in another query.

 

[gemma-the-husky]

the reason this is hard in databases, is determining what constitutes 10 previous records.

Databases are predicated on sets of data, without any employed sort order. In order to average 10 records, you need to have a set of 10 records

So rather than the "10" previous, it is easier for a database to average, say, all records from the previous 1 month, say, however many records that may be.


It is often better to reconsider the request. ie - Why average exactly 10 records in particular?