Sumproduct in Access

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

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


"Job" wrote in message
...
Is it possible to do a SumProduct like function in Access?

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



"Duane Hookom" wrote in message
...
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


"Job" wrote in message
...
Is it possible to do a SumProduct like function in Access?

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" wrote in message
...
Is it possible to do a SumProduct like function in Access?

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

Cheers

"Job" wrote in message
...
Is it possible to do a SumProduct like function in Access?