Understanding Databases – Present Fields By Sum

Present specific fields in a grouped report by sum

The ‘sum’ function will add together all the records in a specified field.

Therefore, the sum function can only be applied to numerical fields.

Reports allow you to present fields as a sum without having to create a query to include the function.

28 Grouped By Sum

An accounts database is an example of where this might be useful. Total monthly sales revenue can be displayed in a report by using the sum function simply by applying it to a ‘takings’ or ‘daily revenue’ field.

To present a specific field as a sum, first open the report in design mode.

Then click on the ‘Label’ icon on the toolbar, and draw a text box in a grouped section.

To change the text in the label, double click the text box, or select properties.

In the Label section of properties, type the text you want to display e.g. ‘Total Bonus’.

29 Text Box

Then click the ‘Text Box’ icon from the toolbar and draw a text box next to the lable.

Double click the text box or select properties.

Select the ‘Data’ Tab from the properties window.

30 Function

To present fields by sum, select the ‘Function’ option from the Data Field Type drop-down menu.

Select the field you want to display the sum for, from the Data Field drop-down menu e.g. Bonus.

From the Function drop-down menu, choose ‘Accumulation’.

From the Scope drop-down menu, choose the group you are displaying the data in.

31 text Report

Execute the report to open the text document.

The text box created will now be displayed as a sum of the selected field.

In this case, all of the bonus values are added together to give a total bonus value for each individual employee record.