question about multiplying

[Tara]

Hello,

I'm trying to create a formula for Z in which Z is equal to the entries of Y
multiplied together (instead of added as in the below example). I've tried
to change the word "sum" to "multiply" or "product" but without any luck!

SELECT Table5.X, Table5.Y, (SELECT sum(Y) AS Z
FROM Table5 AS Dupe
WHERE Dupe.X <= Table5.X) AS Z
FROM Table5
WITH OWNERACCESS OPTION;

Thank you for any help!
Tara

 

[John W. Vinson]

Hello,

I'm trying to create a formula for Z in which Z is equal to the entries of Y
multiplied together (instead of added as in the below example). I've tried
to change the word "sum" to "multiply" or "product" but without any luck!

Back to high school math: sum the log and take the exponent.

SELECT Table5.X, Table5.Y, (SELECT Exp(sum(Log(Y))) AS Z
FROM Table5 AS Dupe
WHERE Dupe.X <= Table5.X) AS Z
FROM Table5
WITH OWNERACCESS OPTION;

John W. Vinson [MVP]

 

[Tara]

Must have been absent that day!
Thanks for your help.

 

[John Spencer]

Per Brian Camire

If "value" contains only positive numbers(no nulls, no negatives, no zero
value), try:
Exp(Sum(Log([t2.value])))

Otherwise, try using the following expression:

IIf(Sum(IIf([Your Field]<0,1,0)) Mod
2=1,-1,1)*Sgn(Min(Abs([YourField])))*Exp(Sum(Log(Abs(IIf([YourField]=0,1,[YourField]))))

For reference:

1. The expression "IIf(Sum(IIf([Your Field]<0,1,0)) Mod 2=1,-1,1)"
evaluates to -1 if there are an odd number of negative values, or 1
otherwise.

2. The expression "Sgn(Min(Abs([Your Field])))" evaluates to 0 if one of
the values is zero, or 1 otherwise.

3. The expression "Exp(Sum(Log(Abs(IIf([Your Field]=0,1,[Your Field]))))"
returns the product of the absolute values, substituting values of 0 with 1.
Substituting values of 0 with 1 is simply a "trick" to avoid the "Invalid
procedure call error". If there is a value of zero, the result of this
expression doesn't matter anyway, since it will be multiplied by the result
of the second expression, which will be zero.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..