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

Counting in a Microsoft Access 2007 Report

While using Microsoft Access 2007 reports sometimes it’s necessary to count how many records are contained in a report. Access 2007 allows for that number to be displayed or let’s you add a line number to each record to make it easier to refer to a particular entry.

Count the number of records in a report or group

There are two ways to add counts to a report, either by using the Layout View or the Design View. The quickest way to add counts to a report is by using the Layout View.

Layout View

In the Navigation Pane, right click the report and choose Layout View:

Fig 1. Right click the report and choose Layout View

Then click a field that you want to count and on the Format ribbon, in the Grouping and Totals group, click on Totals and choose Count Records:

Fig 2. Choose the formula to count records

By choosing Count Records, Microsoft Access 2007 counts all the records in the report regardless of whether there is a value in the selected field. If you need to count only records for which there is a value in the selected field you need to choose Count Values:

Fig 3. Access 2007 added the number of the records (highlighted in purple here)

Note:

When using the Layout View, Access 2007 builds an expression which counts all records that are not null. You can avoid this either by adding counts to fields which don’t allow nulls (like the ID field) or by manually changing the expression Access 2007 uses. In order to do that, go in the Property Sheet in the Data tab and in the Control Source property box, delete the expression and type =Count(*).

Design View

You can also count the record in a report (or subreport) by using Design View. In the Navigation Pane right click the report and choose Design View. In the Design ribbon, go to the Controls group and click Text Box:

Fig4. Click the Text Box in Design View

Click in the Report Header or Footer where you want to place the count box. Make sure the box is selected and then go to the Property Sheet and click the Data tab:

Fig5. Open the Property Sheet

Now, in the Control Source property box, type =Count(*)

And here is the result (the number is highlighted in Purple):

Fig 6. Number of records shown

Add a line number for each record in a report or group

You can number the items by using by using a calculated control and setting its Running Sum property. In the Navigation Pane, right click the report and choose the Design View. . In the Design ribbon, go to the Controls group and click Text Box. In the Details section of the report , drag the mouse to create the text box.

Fig 7. Drag the text box

Go to the Property Sheet in the Data tab and make sure to have all the info as shown below in Fig 8:

Fig 8. The settings for the control box

Now go to the Format tab in the Property Sheet and in the Format property box, type #. (a pound sign followed by a period):

Fig 9. Type the code to format the line number

And this is the result:

Fig 10. The line numbers in the report