databasedev.co.uk - database solutions and downloads for microsoft access

Summing in a Microsoft Access 2007 Report

In any Microsoft Access report which contains numbers, you can use several mathematical operations, such as totals, percentages, averages and running sums. In all cases, they make the data more understandable.

Adding totals and other aggregate functions in Layout View

Before explaining the steps to take in order to add the aggregate functions to Access 2007 reports, let’s take a look at which functions can be added in reports:

Name	Function	Explanation
Sum	Sum()	The sum of all numbers in a column
Average	Avg()	The average of all numbers in a column
Count	Count()	The count of all items in a column (see the tutorial about Counting in Access 2007)
Maximum / Minimum	Max() / Min()	The highest/ lowest value in a column
Standard deviation	StDev()	The standard deviation
Variance	Var()	An estimate of variance

In order to add any of them, follow the steps:

1. Right click the report in the Navigation Pane and choose the Layout View
2. Click the field you want to add a function to (in our example, the sales field)
3. On the Format ribbon, under the Grouping &Tools group click on Totals and choose the function you want (in our example, average)

Fig 1. Add the function to the selected field

Adding totals and other aggregate functions in Design View

Using the design view allows you to have a bit more control over the location and appearance of the control. However, for the general user the Design View can be a bit intimidating.

1. Right click the Report in the Navigation Pane and choose the Design View
2. In the Design ribbon, go to the Controls group and click the Text Box
   
   
   Fig 2. Choose the text box
3. In the report, click where to add the control. In the text part write “Average sales” and then click the control part. Go to the Property Sheet and click the Data tab. Near the Control Source write =Avg(Sales)

Fig 3 Add the control and the function

And here’s the result:

Fig 4 Access 2007 calculates the average sales

Percentages

There’s a bit of a problem if you want to add percentages to reports. Microsoft Access 2007 cannot calculate percentages like Microsoft Excel 2007 does, so you pretty much have to “help” it.

Firstly, you need to add a control to calculate the totals (or sum). You can do that either in Layout View or Design View. Now, you need to go to the Design View and add a control to calculate the percentage. In the Properties Sheet go to the Data tab and add the formula:

=[sales]/sum([sales])

Then go to the Format tab and choose the Percent format for the value. You can also use the Expression builder to add the formula.

Fig 5 Add the formula to calculate the percentage of sales

Obviously if you use groups you can calculate percentages of each item in the group total or percentages of the groups in the grand total. For this example we use the simple percentage of an item in the total (sales of A in the total sales).