Summing Field in Report

[Bruister via AccessMonster.com]

I am trying to sum a field in a report but I end up with a huge number as the
underlying query is returning multiple instances of the numbers being summed.
The query is ok but I basically only want to sum the single instances as
displayed on the report. Whatever I try I get the multiple values in the
query being added. How can I get a sum function in a text box to sum only the
numbers displayed? Failing that is their a way of removing the duplicate
values in the query so only 1 instance appears based on a value in another
field?

 

[Allen Browne]

There are a couple of solutions: subreports, or running sums.

Can you create a grouping on whatever is repeating? If so, you can place a
text box in the group footer section so that it occurs once only (not
repeated in every row in the Detail section.) Set this text box's Running
Sum property to "Over All", and it accumulates the value. If it's name is
Text89, you can then show the correct total by adding another text box to
the Report Footer section, and setting its Control Source to:
=[Text89]

If that won't do the job, the other alternative is to remove the repeating
lines from the report, and show those in a subreport. That way when you sum
the field in the main report it is not duplicated many times.