Wrong Sum in Report Footer

[Katja.Buckley]

I have a problem in my report.
I have a Sum with aggregates and in the detail section, the total is
calculated correctly, in the report footer, however, it is calculating
the sum incorrectly. The total is off by an arbitrary number.

I have a last name header
then Detail
And the last name footer
The total for the footer is as follows:
=Sum(([Projected Sale Price]*[Actual Commission]/100)-([Projected Sale
Price]*[Outside Broker %])/100)*[Inhouse Broker Split]/100

It totals up perfectly correct.

This is what I am using in the report footer:
=Sum(([Projected Sale Price]*[Actual Commission]/100)-([Projected Sale
Price]*[Outside Broker %])/100)*[Inhouse Broker Split]/100

The Grand total is not the actual grand total.

Anybody know what I am doing wrong?

 

[KARL DEWEY]

Are each of these --
[Projected Sale Price]
[Actual Commission]
[Projected Sale Price]
[Outside Broker %]
[Inhouse Broker Split]
from your table or query or are they
object names in the detail section?

 

[Graham Mandeno]

Hi Katja

Where does [Inhouse Broker Split] come from? Is it a field in your
RecordSource? If so, the value that is used to multiply the sum in your
report footer will be the value from the last record. If this value varies
from record to record then you will get inconsistent results.

I'm not certain, because I'm not familiar with your application, But I think
your expression should be:

=Sum((([Projected Sale Price]*[Actual Commission]/100)-([Projected Sale
Price]*[Outside Broker %])/100)*[Inhouse Broker Split]/100)

It might be easier if you add a few calculated fields to your RecordSource
query. Say:

CommissionAmount:
[Projected Sale Price]*[Actual Commission]/100

OutsideBrokerCommission:
[Projected Sale Price]*[Outside Broker %])/100

InhouseBrokerCommission:
([CommissionAmount]-[OutsideBrokerCommission])*[Inhouse Broker
Split]/100

Then you can just use =Sum([InhouseBrokerCommission])

I'm guessing the other two calculated fields could come in handy for other
columns as well!