Numbering and Counting Records in an Access 2007 Report

Access reports provide many options for formatting the data in the report so that it is easy for readers to understand. One formatting element you may want to include is numbering of the records in the report. Adding numbers allows readers to see a rank order of the items listed or to easily reference a particular record in a discussion.

 Numbered List

To include record numbers you need to add a text box to the detail band and set its Control Source and Running Sum properties correctly. To add record numbers, with the report in design view:
1) On the Design tab of the ribbon, click the text box icon.
2) To add the numbers to each record, click at the left side of the detail band. (When you click you see both the text box and the accompanying label. You can just delete the label control.)
3) Open the property sheet to see the properties of the text box.
4) On the Data tab of the Property Sheet, set the Control Source property to:  =1
5) Also on the Data tab, set the Running Sum property:
a. To “Over All” if you want numbering to be continuous through the report
b. To “Over Group” if you want numbering to restart for each group
NOTE: In Report View or Print Preview some numbers may not appear. If this happens, try scrolling on the page to refresh the display.

Access 2007 provides an easy method for adding subtotals to grouped records in reports. You can automatically create subtotals and a grand total for a field of information in the report using the new view available in 2007, Layout View.

1) Switch to Layout view of the report.
2) Click any value in the field you would like to subtotal. In the pictured example below the records with data in the Date Competed field are being added.
3) On the Ribbon’s Format tab, in the Grouping & Totals group, click the drop down arrow next to Totals and click Sum.
At the end of each group in the report, you should then see a subtotal of the values in the field selected. A grand total of the values in that field will also appear at the end of the report. If needed, labels for those values can be added using the label tool.

 Subtotal in report




Leave a Reply

Your email address will not be published. Required fields are marked *