Advanced Database – Format arithmetic calculation controls in a report

We are going to use the query that we created in a previous tutorial.

The query is shown to the left, it calculates the total hours worked on each job for each member of staff.

1 Format Arithmetic Calculation

Then the query calculates and displays the average number of hours worked for each staff member.

We are going to create a report to display this data.

2 Query

Create a report in wizard mode.

Add both fields from the query.

Click Finish as we are not too concerned with the layout.

Base will generate and open a report document in Writer.

3 Report

The report displays the staff number and the average of the total hours.

It gives the average rounded to 2 decimal places.

We may want to change this to 1 decimal place.

Also, we probably want to change the column title from Total Hours to Average Total Hours to represent the data more accurately.

4 Report Design

Open the report in design view by right clicking and selecting edit.

Right click on the text box that displays ‘=Total Hours’ and select Properties

Click the General tab, then click the ‘…’ symbol next to Formatting

Here you can change how the data is displayed. Select Category->Number then in Options change Decimal places to 1. Click OK.

5 Options

Now right-click on the Total Hours title text box and select properties.

In the Label section, change the text to: Average Total Hours.

Save and close the report.

Generate the report by double clicking it in the Reports section of the main database window.

6 Save Report

Notice that the new report displays the revised column title and rounds the values to 1 decimal place.

The same method can be used to control the way percentages, currency, etc. are formatted and displayed in a report.