a better way to get a sum in a report

I need to get a sum in a group footer in a report, I wrote a public function
in the module, and wrote an expression to check the condition and calculate:

=Sum((IIf(If_Instrument([ItemNumber])=1,[Jan_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jan_D],0)+IIf(If_Instrument([ItemNumber])=1,[Feb_F],0)-IIf(If_Instrument([ItemNumber])=1,[Feb_D],0)+IIf(If_Instrument([ItemNumber])=1,[Mar_F],0)-IIf(If_Instrument([ItemNumber])=1,[Mar_D],0)+IIf(If_Instrument([ItemNumber])=1,[Apr_F],0)-IIf(If_Instrument([ItemNumber])=1,[Apr_D],0)+IIf(If_Instrument([ItemNumber])=1,[May_F],0)-IIf(If_Instrument([ItemNumber])=1,[May_D],0)+IIf(If_Instrument([ItemNumber])=1,[Jun_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jun_D],0)+IIf(If_Instrument([ItemNumber])=1,[Jul_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jul_D],0)+IIf(If_Instrument([ItemNumber])=1,[Aug_F],0)-IIf(If_Instrument([ItemNumber])=1,[Aug_D],0)+IIf(If_Instrument([ItemNumber])=1,[Sep_F],0)-IIf(If_Instrument([ItemNumber])=1,[Sep_D],0)+IIf(If_Instrument([ItemNumber])=1,[Oct_F],0)-IIf(If_Instrument([ItemNumber])=1,[Oct_D],0)+IIf(If_Instrument([ItemNumber])=1,[Nov_F],0)-IIf(If_Instrument([ItemNumber])=1,[Nov_D],0)+IIf(If_Instrument([ItemNumber])=1,[Dec_F],0)-IIf(If_Instrument([ItemNumber])=1,[Dec_D],0))*[StandardCost])

as you can see, it's a long long expression, and the function If_Instrument
is what I wrote in the module, this works, but it would take me 10 minutes to
get the result, is there a better way to do it? the point is I need to judge
the item number which is in the detail part.

Thanks a lot in advance!

not sure i understand your calculated expression. when the calculation runs,
is the value of [ItemNumber] the same *at that point in time*? if so, seems
like the calculation is saying: if the return value of If_Instrument = 1,
add/subtract all these fields together and multiply by [StandardCost].
otherwise, add/subtract all these zeros together and multiply by
[StandardCost].

see if you get the correct result by changing your calculation to:

=Sum(IIf(If_Instrument([ItemNumber])=1,([Jan_F]-[Jan_D]+[Feb_F]-[Feb_D]+[Mar
_F]-[Mar_D]+[Apr_F]-[Apr_D]+[May_F]-[May_D]+[Jun_F]-[Jun_D]+[Jul_F]-[Jul_D]+
[Aug_F]-[Aug_D]+[Sep_F]-[Sep_D]+[Oct_F]-[Oct_D]+[Nov_F]-[Nov_D]+[Dec_F]-[Dec
_D])*[StandardCost], 0))

if the above doesn't work for you, please explain the logic behind your
original calculated expression and post the code for the function, and i'll
see if i can come up with an alternative.

hth


"lily" wrote in message
...
I need to get a sum in a group footer in a report, I wrote a public
function
in the module, and wrote an expression to check the condition and
calculate:


=Sum((IIf(If_Instrument([ItemNumber])=1,[Jan_F],0)-IIf(If_Instrument([ItemNu
mber])=1,[Jan_D],0)+IIf(If_Instrument([ItemNumber])=1,[Feb_F],0)-IIf(If_Inst
rument([ItemNumber])=1,[Feb_D],0)+IIf(If_Instrument([ItemNumber])=1,[Mar_F],
0)-IIf(If_Instrument([ItemNumber])=1,[Mar_D],0)+IIf(If_Instrument([ItemNumbe
r])=1,[Apr_F],0)-IIf(If_Instrument([ItemNumber])=1,[Apr_D],0)+IIf(If_Instrum
ent([ItemNumber])=1,[May_F],0)-IIf(If_Instrument([ItemNumber])=1,[May_D],0)+
IIf(If_Instrument([ItemNumber])=1,[Jun_F],0)-IIf(If_Instrument([ItemNumber])
=1,[Jun_D],0)+IIf(If_Instrument([ItemNumber])=1,[Jul_F],0)-IIf(If_Instrument
([ItemNumber])=1,[Jul_D],0)+IIf(If_Instrument([ItemNumber])=1,[Aug_F],0)-IIf
(If_Instrument([ItemNumber])=1,[Aug_D],0)+IIf(If_Instrument([ItemNumber])=1,
[Sep_F],0)-IIf(If_Instrument([ItemNumber])=1,[Sep_D],0)+IIf(If_Instrument([I
temNumber])=1,[Oct_F],0)-IIf(If_Instrument([ItemNumber])=1,[Oct_D],0)+IIf(If
_Instrument([ItemNumber])=1,[Nov_F],0)-IIf(If_Instrument([ItemNumber])=1,[No
v_D],0)+IIf(If_Instrument([ItemNumber])=1,[Dec_F],0)-IIf(If_Instrument([Item
Number])=1,[Dec_D],0))*[StandardCost])

as you can see, it's a long long expression, and the function
If_Instrument
is what I wrote in the module, this works, but it would take me 10 minutes
to
get the result, is there a better way to do it? the point is I need to
judge
the item number which is in the detail part.

Thanks a lot in advance!

One obvious problem is that you have created a spreadsheet and are calling
it a table. Each month's data should be in its own row in a separate child
table rather than stored as columns in a single row. With a proper table
structure, you would be able to make use of aggregate functions. Do some
reading on Normalization to understand the problem.

Aside from that, you didn't post the code for your function so I have no
idea what it is doing. In any case, it shouldn't change from clause to
clause of the expression so I don't know why you are checking it 24 times.

"lily" wrote in message
...
I need to get a sum in a group footer in a report, I wrote a public
function
in the module, and wrote an expression to check the condition and
calculate:

=Sum((IIf(If_Instrument([ItemNumber])=1,[Jan_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jan_D],0)+IIf(If_Instrument([ItemNumber])=1,[Feb_F],0)-IIf(If_Instrument([ItemNumber])=1,[Feb_D],0)+IIf(If_Instrument([ItemNumber])=1,[Mar_F],0)-IIf(If_Instrument([ItemNumber])=1,[Mar_D],0)+IIf(If_Instrument([ItemNumber])=1,[Apr_F],0)-IIf(If_Instrument([ItemNumber])=1,[Apr_D],0)+IIf(If_Instrument([ItemNumber])=1,[May_F],0)-IIf(If_Instrument([ItemNumber])=1,[May_D],0)+IIf(If_Instrument([ItemNumber])=1,[Jun_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jun_D],0)+IIf(If_Instrument([ItemNumber])=1,[Jul_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jul_D],0)+IIf(If_Instrument([ItemNumber])=1,[Aug_F],0)-IIf(If_Instrument([ItemNumber])=1,[Aug_D],0)+IIf(If_Instrument([ItemNumber])=1,[Sep_F],0)-IIf(If_Instrument([ItemNumber])=1,[Sep_D],0)+IIf(If_Instrument([ItemNumber])=1,[Oct_F],0)-IIf(If_Instrument([ItemNumber])=1,[Oct_D],0)+IIf(If_Instrument([ItemNumber])=1,[Nov_F],0)-IIf(If_Instrument([ItemNumber])=1,[Nov_D],0)+IIf(If_Instrument([ItemNumber])=1,[Dec_F],0)-IIf(If_Instrument([ItemNumber])=1,[Dec_D],0))*[StandardCost])

as you can see, it's a long long expression, and the function
If_Instrument
is what I wrote in the module, this works, but it would take me 10 minutes
to
get the result, is there a better way to do it? the point is I need to
judge
the item number which is in the detail part.

Thanks a lot in advance!

Hi Tina and Pat,

Thank you very much for your help! both of your suggestions are very
helpful, I changed it and it works fine now, take only 20 seconds to get the
report.

I really appreciate your help!

"Pat Hartman" wrote:

One obvious problem is that you have created a spreadsheet and are calling
it a table. Each month's data should be in its own row in a separate child
table rather than stored as columns in a single row. With a proper table
structure, you would be able to make use of aggregate functions. Do some
reading on Normalization to understand the problem.

Aside from that, you didn't post the code for your function so I have no
idea what it is doing. In any case, it shouldn't change from clause to
clause of the expression so I don't know why you are checking it 24 times.

"lily" wrote in message
...
I need to get a sum in a group footer in a report, I wrote a public
function
in the module, and wrote an expression to check the condition and
calculate:


=Sum((IIf(If_Instrument([ItemNumber])=1,[Jan_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jan_D],0)+IIf(If_Instrument([ItemNumber])=1,[Feb_F],0)-IIf(If_Instrument([ItemNumber])=1,[Feb_D],0)+IIf(If_Instrument([ItemNumber])=1,[Mar_F],0)-IIf(If_Instrument([ItemNumber])=1,[Mar_D],0)+IIf(If_Instrument([ItemNumber])=1,[Apr_F],0)-IIf(If_Instrument([ItemNumber])=1,[Apr_D],0)+IIf(If_Instrument([ItemNumber])=1,[May_F],0)-IIf(If_Instrument([ItemNumber])=1,[May_D],0)+IIf(If_Instrument([ItemNumber])=1,[Jun_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jun_D],0)+IIf(If_Instrument([ItemNumber])=1,[Jul_F],0)-IIf(If_Instrument([ItemNumber])=1,[Jul_D],0)+IIf(If_Instrument([ItemNumber])=1,[Aug_F],0)-IIf(If_Instrument([ItemNumber])=1,[Aug_D],0)+IIf(If_Instrument([ItemNumber])=1,[Sep_F],0)-IIf(If_Instrument([ItemNumber])=1,[Sep_D],0)+IIf(If_Instrument([ItemNumber])=1,[Oct_F],0)-IIf(If_Instrument([ItemNumber])=1,[Oct_D],0)+IIf(If_Instrument([ItemNumber])=1,[Nov_F],0)-IIf(If_Instrument([ItemNumber])=1,[Nov_D],0)+IIf(If_Instrument([ItemNumber])=1,[Dec_F],0)-IIf(If_Instrument([ItemNumber])=1,[Dec_D],0))*[StandardCost])

as you can see, it's a long long expression, and the function
If_Instrument
is what I wrote in the module, this works, but it would take me 10 minutes
to
get the result, is there a better way to do it? the point is I need to
judge
the item number which is in the detail part.

Thanks a lot in advance!