product query

[Bry]

I am trying to get the product of a series of numbers from
within a query. This will be used to calculate the yield
of a process. Can anyone offer any advice on how to
accomplish this. Thank you

 

[Joe Fallon]

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.