the Manx Man's MS Access Pages
to subscribe - click here

Group Totals of Custom Fields on a Report (028)

A number of you have asked this question, so here is the answer for all to see.

You have created a report and in the detail section you need one or more fields that are based on custom calculations, based on fields from the source table. I can think of three (3) ways to accomplish this.

1. create custom fields (calculated fields) in a query that then is the source for the report
2. create custom fields in the report with control sources that have the calculation
3. same as #2 but with complex calculations based on some VBA

To have group sub-totals in the first case, just have fields in the group footer with a control source like "= sum([calcfieldnamefromthequery])".

The second case can be done this way. If the control source for the custom field is "= [fieldA] * [fieldB] / 100" then in the group footer put the field meant for the total and have a control source of "= sum([fieldA] * [fieldB] / 100)".

If, however, the calculated field is based on some more complex calculation, the third method of doing a sum may be better than method #2. This third method takes advantage of the Running Sum property.

• put a hidden textbox in the detail section
• make its control source "= [calculatedfieldname]"
• set its Running Sum property to "Over Group"
• put a textbox in the group footer
• make its control source "= [hiddenfieldname]"

Whenever possible I prefer method #1.  In fact, I rarely use a table as the direct record source for a report. And since a query can refer to a custom function, I can have very complex logic determine the value.