Problems with Calculations 2

[mearmortal]

One footer contains the following: -
[Text39]=Sum([Key])
[Text41]=Sum([DefectQuantity])
[Text43]=[OFDNumber]

[DPMONumber]=([text41]/([text39]*[Text43]))*1000000

this works
the Next footer contains: -
[SumofDPMONumber]=Sum([DPMONumber])

this doesn't work, can anyone explain why? and a possible remedy?

 

[DonQuichote]

It does not work because [DPMONumber] is not a field in the report's underlying table or query. Maybe you can add a field to the query that you can sum over?

 

[scriverb]

Yes, DonQuichote is correct about calculation field. You cannot perform a Sum on a already calculated field. But you can perform a modification of the original equation as long as you refer to the detail sections text objects. Try this:

[SumofDPMONumber]=(Sum([text41])/(Sum([text39])* Sum([Text43])))*1000000

Let me know if this works.

Bob Scriver

 

[DonQuichote]

Please be careful about that. The sum of products is not the product of the sum (2*3 + 4*5 is not (2+4)*(3+5)). The same holds for division. What you can do is create an extra field that holds the formula ([text41]/([text39]*[Text43]))*1000000 in the query. However, the fields in that formula are sums themselves. Looks more like a subreport/mainreport question to me. You can put the fields in the underlying query of the main report

 

[scriverb]

DonQuichote, yes you are right. Sorry about that. Should have thought that one through better and/or tested it before firing it off.

My apologies. I believe that was a late night posting.

Bob Scriver

 

[mearmortal]

Because I didn't receive these responses in my mail until this morning, I did not check for the responses.

I finally got round this one by creating multiple queries calculating the majority of the figures for me without the need to do it on the report.

DPMOValue: ([SumofDefectQuantity]/([SumofKey]*[OFDNumber]))*1000000

Then I just put the summed values into the equation and hey presto its done.

Even got the pecentage spread in as well.

=IIf(([DPMOValue]/[SumofDPMOValue])=Null,0,([DPMOValue]/[SumofDPMOValue]))
And this is a percentage type field.

Thanks for all your suggestion and watch out for those Sign priorities.