Percent totals

[onmars]

I am trying to total percents in a report.

I have an underlying table with the fields [CurrentRent] and
[IncreaseAmount]. In a report query I have created the expression field
NewRent[CurrentRent]+[IncreaseAmount]) and the expression field
Percent: ([NewRent]-[CurrentRent])/([CurrentRent])/1.

In the detail section of the report the Percent field calculates
correctly. But, when I sum the Percent field =Sum([Percent]) in the
report footer, the total is incorrect.

I believe that I need to make a totals query to add to my report query
to get the correct percent total in the footer, but I can't seem to
quite figure out what fields belong in the totals query and or get
summed in the report query.

Thanks.

 

[Larry Linson]

"incorrect" covers a lot of territory, and, as they often say, the devil is
in the details. Perhaps with a little more detail on what you have and what
you want, someone can offer a useful suggestion.

I have one comment: NewRent = CurrentRent+IncreaseAmount, so instead of
using NewRent - CurrentRent in the second calculation, would it not be
simpler and more straightforward to use

Percent: ([IncreaseAmount])/([CurrentRent])

to obtain the percent of increase as a fraction. I am not at all certain
what the final "/1" accomplishes?

Larry Linson
Microsoft Access MVP

 

[onmars]

Thanks for responding.
I have 4 columns of records.
A record example would be CurrentRent -$1075, IncreaseAmount-$10,
NewRent-$1085, Percent-.0093%.
The Page sums are sumCurrentRent-$6185, sumIncreaseAmount-$185,
sumNewRent-$6385, sumPercent 18.64%. I want the percent increase to be
a sum of [sumIncreaseAmount]/[sumIncreaseRent]. I would expect a result
of .0299% instead of the 18.4 percent I have been receiving.
I hope these details help.

 

[Larry Linson]

Thanks for responding.
I have 4 columns of records.
A record example would be CurrentRent -$1075, IncreaseAmount-$10,
NewRent-$1085, Percent-.0093%.
The Page sums are sumCurrentRent-$6185, sumIncreaseAmount-$185,
sumNewRent-$6385, sumPercent 18.64%. I want the percent increase to be
a sum of [sumIncreaseAmount]/[sumIncreaseRent]. I would expect a result
of .0299% instead of the 18.4 percent I have been receiving.
I hope these details help.

You cannot use the Sum function on Calculated Fields, which appears to be
what you are trying to do... but, the numbers you use to illustrate indicate
you want sumIncreaseAmount divided by sumCurrentRent, shown as a percent,
not a sum of those numbers.

If you want this in the Report Footer, and the Fields in the underlying
Query are [IncreaseAmount] and [CurrentRent], then a Control with
ControlSource of = Sum([IncreaseAmount])/Sum([CurrentRent]) with the format
of Percent should do what you want.

If I have misunderstood, please clarify.

Larry Linson
Microsoft Access MVP

 

[onmars]

Thank you very much for providing the answer despite my vague and
cloudy details of the problem. Much appreciated!