I love this question because the answer is so unexpected, yet simple.
===============================================
Given a table named Items with 5 records and a field named Qty. The 5
values are 2,3,3,1,2.
The answer to the question of what is the value of all the records
mulitplied together, in this case is obviously 36.
To get a query to do it use this SQL statement:
SELECT Exp(Sum(Log([qty]))) As Answer FROM Items
Answer = 36
The reason this works is because of this logarithm formula:
Given: log(xy) = log(x) + log(y)
and exp(log(xy) = xy
Substitution
implies: exp(log(x) +log(y)) = xy
If x and y are field values named [qty] in a two record table then:
exp(sum([qty])) = xy
Conclusion: There is no reason to limit the table to two records so the
formula
will in fact yield the multiplication of all the records in the qty field.
exp(sum([qty])) = multiplication of all records in [qty]
You have to use some logic to avoid the value of 0. If one of the values in
the column is zero then the answer of a mulitplication of all values is
obviously zero. But the SQL statement above will bomb because Log(0) is
undefined. So you need to use a preliminary search for the obvious answer of
zero
before trying this.
