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

  • Thread starter Thread starter hans.chys
  • Start date Start date
H

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