PivotTable calculated total using an expression

E

EnviroGirl

In Access 2003 you are supposed to be able to use an expression to calculate
a custom total. See the topic "About calculating totals in PivotTable view".
I can't get this to work at all. Whenever I enter the expression for the
custom calculation I get a message like "syntax error token is not valid".
Is there some special way of writing an expression for this type of
calculation. The one example in the above mentioned topic uses the text
"[measures].[sales]" to refer to the sales field. Why "[measures]"? I can
get the standard automatic calculations like SUM and COUNT but I also want a
GEOMEAN and am having no luck. Unfortunately I can not seem to find a way to
look at the formulas for the autocalcs, such as SUM, to get a good example.
Any advice?
 
F

Fons Ponsioen

What about providing some information, i.e., what is the
table or query structure or data element names that you
are working with, and what is the formula you have created
and/or what are you trying to accomplish. Maybe someone
can help with that information.
Fons
 
G

Guest

You are right. I will try to be more descriptive and
specific with my question.

I have an Access query that results in a datasheet that
looks like this:

Matrix Detail LabID Analyte Result
mammal Shrew 23 x 4
mammal Shrew 23 y 6
mammal Shrew 23 z 7
mammal Shrew 55 x 12
mammal Shrew 55 y 10
mammal Shrew 55 z 8
mammal mouse 42 x 16
mammal mouse 42 y 13
mammal mouse 42 z 7
mammal mouse 38 x 15
mammal mouse 38 y 5
mammal mouse 38 z 3

I have set up a pivot table with
row fields: Matrix, Detail, LabID
Column field: Analyte
Data: Result
If I click on Result and then the autocalc button I can
easily get calculations for Sum, Average and the other
basic choices but what I would like is to use the button
for "Creat a calculated Total" to calculate something that
is not a choice in the auto calc list like a geometric
mean for example. From all the help I have read it seems
like it should be possible in theory but I can't see to
write an expression for the calculation that does not
result in an error message.

I would be very happy to do this in an Access or Excel
pivot table but it is my impression that custom
calculations for totals are only possible in Access pivot
tables.

Can anyone give me guidance on how to accomplish this type
of custom total calculation?

Thanks for your patience!

-----Original Message-----
What about providing some information, i.e., what is the
table or query structure or data element names that you
are working with, and what is the formula you have created
and/or what are you trying to accomplish. Maybe someone
can help with that information.
Fons
-----Original Message-----
In Access 2003 you are supposed to be able to use an expression to calculate
a custom total. See the topic "About calculating totals in PivotTable view".
I can't get this to work at all. Whenever I enter the expression for the
custom calculation I get a message like "syntax error token is not valid".
Is there some special way of writing an expression for this type of
calculation. The one example in the above mentioned
Click to expand...
topic
uses the text
"[measures].[sales]" to refer to the sales field. Why "[measures]"? I can
get the standard automatic calculations like SUM and COUNT but I also want a
GEOMEAN and am having no luck. Unfortunately I can not seem to find a way to
look at the formulas for the autocalcs, such as SUM, to get a good example.
Any advice?
.
Click to expand...
.
Click to expand...
 
Top