Average and SD of last 25 records

[paulfenwick]

Hi, I need a query to find the running average and running Standard Deviation of one of the fields of the last 25 records in a table.

Is this possible?

I though that I could create a query to seqentially number the records (somehow?) then link that to a query that has the criteria (<= No and >No-25) - but is there an eaiser way?

Thanks

 

[PHV]

Have a look at the TOP 25 predicate.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[paulfenwick]

Hi PH

I didnt explain myself well - sorry!

I need each row of the recordset to calculate the average of the last 25 records - so for record 50, its calculates the average of records 26 to 50, for record 51, it calculates the average of records 27 to 51 and so on.

Easy in Excel but how can I do this in access?

 

[MichaelRed]

The 'easy' way might be to just use brute force. Knowing the record sequence (ordering) is Key to getting consistient results is any case, so make sure you have the recordset indexed or ordered the same way all the time.

The brute force approach -off the top- a query to select the top 25 based on the selection of the records which are greater than or ezual to a primary key (ordered by this also) then the standard stat functions are applied to this set and the first record's fields are updated to these values. Increment / advane the Key and go again.

It is necessary, in this process, that the ordering be on a "PrimaryKey", as this prevents duplicate values being included in the top N.

For relatively small recordsets, the above will only be boring. For moderate and larger recordsets, it is quite possibly terminially boring.




MichaelRed