Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Average and SD of last 25 records

Status
Not open for further replies.

paulfenwick

Technical User
Sep 16, 2004
20
GB
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
 
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
 
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?
 
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


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top