Passing report values to module

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I want to calculate a 95th percentile. I have referenced the Excel object
library and can call a function to calculate it. However, I want to put this
function on a report. I have put it in the summary section but when I pass
the value using function([Value]) Access only passes the last record from the
detail section and does not pass an array with all the records. How can I
pass all records in the detail section of a report to a module and then
display on the report?

Martin
 
I want to calculate a 95th percentile.
Click to expand...

select top 1 value
from
( select top 95% value
from mytable
order by value desc
)
order by value asc;


but this only works if you have much more than 100 values. If you need to
do interpolation between values, you are probably better off working with
the recordset in Excel. For example, if you have 27 values, you will need

0.65 * val25 + 0.35 * val26

where val25 and val26 are the 25th and 26th highest values respectively
(I think so: this is off the top of my head). Then again, you might be
best off working with a dedicated stats package, which will do it right
and in a fraction of the time!

All the best


Tim F
 
Back
Top