How to multiply percentages in a CROSSTAB-action (instead of e.g. SUM)

[hans.chys]

I'm looking for a solution to MULTIPLY percentages in order to come
to
a 'summed' total percentage via e.g. a TRANSFORM function in a
crosstab-query (MS Access 2003)

Example:


record 1 - 0,95 - 2007
record 2 - 0,85 - 2008
record 3- 0.62 - 2008
record 4 - 0.12 - 2008
record ...


I want to have a crosstabquery that results in a column per year
(2007, 2008, ...) and where the total is the 'multiplication' instead
of the SUM.


e.g.. for 2008, the result should give : 0,85 x 0,62 x 0,12 =
0,063...


Is this possible by using an SQL-command (which), or do I need to use
a VBA-module ?


If so, how should that module look like ?


Thanks for sharing your ideas on this !!


Hans
Belgium

 

[Guest]

dim rs as dao.recordset
dim dblTotal as double

Set rs = currentdb.openrecordset("datasource")
With rs
while not .eof
dblTotal = dblTotal * [fieldname]
.movenext
loop

End with

 

[John Spencer]

Try following expression to multiply the numbers together.

Exp(Sum(Log([YourField])))

Warning the numbers in your field must be greater than zero - No nulls, no
zeroes, no negative numbers. Also if your result gets too large then you
will get an error. Since your values as posted were all between 0 and 1
(not inclusive) you should have no problem


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

 

[hans.chys]

Thanks to all for the feedback. It helped me out just fine !!

The logaritmic approach works, although I had to use some intermediate
queries to do the math.
The expression (EXP(SUM(LOG ....) gives an error message when done in
a crosstab. But by splitting up over some subqueries, I go the correct
results.

Greetings,
Hans Chys,
Belgium.