Sum by multiple

  • Thread starter Thread starter sq75222
  • Start date Start date
S

sq75222

Dear all, I need the help on below;

Colunm1
0.9
0.89
0.87

I need to show my results as 0.69687 (0.9*0.89*0.87) in query.
 
I don't think Access provides a way to multiple the values in a column, so
you will probably need to write a VBA function.

OpenRecordset() and loop through the values to get the product.
 
SELECT Exp(Sum(Log([TheField]))) as Multiplied
FROM TheTable
WHERE TheField > 0


Per Brian Camire

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

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
..
 
Thank you, John Spencer. It work

John Spencer said:
SELECT Exp(Sum(Log([TheField]))) as Multiplied
FROM TheTable
WHERE TheField > 0


Per Brian Camire

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

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
..

sq75222 said:
Dear all, I need the help on below;

Colunm1
0.9
0.89
0.87

I need to show my results as 0.69687 (0.9*0.89*0.87) in query.
Click to expand...
Click to expand...
 
Of course, John!

Like Noah nailing together some of the timber from the ark to help the
snakes to go forth and multiply: even adders can multiply with a log table.
:-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

John Spencer said:
SELECT Exp(Sum(Log([TheField]))) as Multiplied
FROM TheTable
WHERE TheField > 0


Per Brian Camire

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

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
.

sq75222 said:
Dear all, I need the help on below;

Colunm1
0.9
0.89
0.87

I need to show my results as 0.69687 (0.9*0.89*0.87) in query.
Click to expand...
Click to expand...
 
Back
Top