Summing a field only once

[cantona]

I have a report with the data in the following format

Course Code Title 16-18 19+ Applications
16-18 AB1 Test 0 20 3
19+ AB1 Test 0 20 15



I need to add a total under the 19+ columns which only sums the field once for each course code. It will appear twice as each course code is split between 16-18 year olds and 19+ . In the example above the total should be 20. I then need to add a grand total which only totals the field once for each course code. What is the best way of achieving this?

 

[dhookom]

In the group footer use a text box with a control source of just the field without the =Sum(...).

For grand totals, I usually create a query with the grand total calculation and add this query to the report's record source. You can also duplicate the text box above from the group footer like:
Name: txtRunSum19Plus
Control Source: [19+ Field Name]
Running Sum: Over All
Visible: No
Then add a text box to your report footer:
Control Source: =[txtRunSum19Plus]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]