Create Better MS Access Reports
Report Builder for Microsoft Access helps you create dynamic, professional-looking reports fast! The easy-to-understand wizard helps you with complex tasks like calculated fields, adding subreports, customizing styles, as well as grouping and sorting. Download a free trial today!
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.
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)
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(*).
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
Microsoft Access is an application used to create small and midsize computer desktop databases for the Microsoft Windows family of operating systems. It can also be used as a database server for a web-based application.
This electronic book (ebook) provides lessons on how to use Microsoft Office Access 2007 to create and manage databases. The lessons follow a step-by-step format with practical examples.
Download the ebook now - Microsoft Office Access 2007 Desktop Databases