means in reports

[Tom Rogers]

I want to generate a weighted mean from records in a
report. each record is a size with a corresponding
quantity. To calculate a weighted mean I need to Sum the
quantities, multiply the size by the quantity/sum
quantity and then Sum the resulting values. I guess an
expression would look something like this;
Sum(([size]*quantity/Sum [quantity]))
Access doesn't allow me to do this on the report design
view
This is my first go at this sort of thing, so i'm not
sure whether I can't actually do this or i'm doing it the
wrong way.
Thanks for any help

 

[SA]

Tom:

Actually your calculation is incorrect for the weighted mean; it would be:

Sum([size]*quantity)/Sum ([quantity])

That not withstanding, once you see that it should be pretty easy to do in
your report.
For your quantity control, alias its name to something like Qty and for its
control source, change it to =CLng(NZ([Quantity],0)), this will convert any
nulls to 0 to allow the sum function to always work.

Create one unbound control on your report and set its visible property to be
false. Lets call name WgtQty. Set its control source to be:
= CLng(NZ([Size]*[Qty],0)). This will again convert any nulls to 0 for
the Weighted Quantity, allowing the sum function to work.

Then in the report footer, add three unbound controls, two of which have
their visible property set to false.

Control 1: Name: SumQty, ControlSource: =Sum([Qty], Visible = False
Control 2: Name: SumWgtQty, ControlSource: =Sum([WgtQty]), Visible = False
Control 3: Name: WgtMean, ControlSource = IIF([SumQty]>0,
[SubWgtQty]/[SumQty],0), Visible = True

That should do it.