Help simulating PRODUCT() in MS Access SQL query

[jennifer_hazen]

Hi all - new to this group and having trouble with Access.

My goal is to take calculate a product in a "Group By" query, rather
than a sum. I've been doing some research and found a workaround using
the SQL statements POWER and LOG10 in combination with SUM (see:
http://www.sql-server-performance.com/user_defined_functions.asp).

Sounds great. But I can't seem to get it to work.

I create a "Group by" select query, using SUM as the expression

e.g. SUM (mytable.myvariable)

then modify it to:
POWER(10,SUM(LOG10 (mytable.myvariable)

And receive this error:
Undefined function 'POWER' in expression.

Does Access 2000 not recognize the terms POWER or LOG10? Or am I doing
something else wrong? Any suggestions?

Thanks! Jen

 

[Graham Mandeno]

Hi Jen

You're right. Access has neither a POWER function, nor a LOG10 function.

POWER is done with the ^ operator. For example, 10^2 is 100.

LOG10 can be calculated with the Log function (natural log):
LOG10( number ) = Log( Number ) / Log(10)

Combining these, you can say:
Power: 10^Sum(Log([MyField])/Log(10))

 

[jennifer_hazen]

Thanks for the help. Finally got this working. One hurdle was that I
couldn't do the entire calculation in the totals-type query. Another
hurdle was that some of the values in [MyField] are zeros, so
calculating Log ([MyField]) gives an Error.

The workarounds I came up with are as follows:
In my original table, I calculated the second half of the expression:
Expr1: Log[MyField] / Log(10)
However, as I had zeros, and didn't want a Error to occur, I used an
Iif Statement to temporarily fill in a 0 for the result wherever
[MyFIeld]=0: Expr1: IIf([MyField]=0, 0 ,Log([MyField])/Log(10))

In the totals query, I calculated the sum of Expr1 from the original
table as: SUM ([Expr1])

Then, I created a new column in the query that raised 10 to the power
of [Sum of Expr1]): Product: 10^[Sum of Expr1].

This works in all cases except where there is a 0 in [MyField]

So, to address the 0 problem, I again used an Iif statement. Logically,
any value of 0 in [MyField] should give a final result of 0 to the
product of the original data. As all of my data are >=0, to identify
the cases where a 0 was in the original data, I created another column
in the query that took the Min of [MyField]. I then modified the
column that raised 10 to the power of [Sum of Expr1] to become an
if/then statement as follows: Product: IIf([Min of MyField]=0, 0
,10^[Sum Of Expr1]

Glad Access keeps things interesting!
-Jen