Sumproduct in Access

[Job]

Is it possible to do a SumProduct like function in Access?

 

[Duane Hookom]

If I understand correctly assuming you have values in a field [Num] with
values fo 2, 3, and 4 you would want a result of 2*3*4 or 24? If so, you can
create a totals query and use a calculation that uses Exp() against the
Sum() of the Log() of the Num


Exp(Sum(Log([Num])))

 

[Job]

Duane,

For example, I have columns [FTE] and [DaysToFinish]
The values may be .7,.6,.4 and 3.5,2.9,2.2 respectively. The sumproduct
would take (.7 * 3.5) +(.6*2.9)+(.4*2.2) = 5.07

Job

If I understand correctly assuming you have values in a field [Num] with
values fo 2, 3, and 4 you would want a result of 2*3*4 or 24? If so, you
can create a totals query and use a calculation that uses Exp() against
the Sum() of the Log() of the Num


Exp(Sum(Log([Num])))

--
Duane Hookom
MS Access MVP

Is it possible to do a SumProduct like function in Access?

 

[Ed Robichaud]

As you've discovered there is no SumProduct function in Access. You can
somewhat roll your own, by using a form, with a grid layout of the fields
involved, then creating unbound controls (which need not be visible) to hold
the products, then use more of those unbound controls to hold the
expressions summing those products.

You might also want to look at X-tab queries to do sums, then add an
expression to handle the multiplication.
-Ed

 

[Job]

Thanks for all the input. I'll look into the x tab queries etc...

Cheers