Counting in reports

by Sal Ricciardi

How many orders did you receive during your last reporting period? In this article, learn how to count items and include that value in your Access reports.

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002

Question: How do I show a count of the number of items in a report?

Sometimes you need to not only sum a value, but count the number of items. For instance, you might want to show the number of orders for a particular product in addition to a sum of the individual sales amounts. To count the orders, you can use the Count aggregate function. Access uses the Structured Query Language (SQL) to query your database, and Count is one of the SQL aggregate functions that you can use to perform calculations on column values.

Using the Count function

Suppose you have a Sales by Product report and you want to add a count of the number of orders. To count the number of orders, you create a text box control and set its ControlSource property to an expression that looks like this:

=Count(*)

The asterisk tells Access to count all of the rows in your Detail section, but the result depends on where you place the text box. As with calculating a sum, the section of the report where you place the text box is important, because Access determines how to aggregate the count based on the report section you choose. If you place the text box control in the group header or footer, the count will include only the detail rows for that group. If you place the text box control in the report header or footer, the count includes all of the details rows for the entire report. In the following diagram, the expression "Count(*)" is shown in the Control Source property box for the text box, and the total generated by the Count function is shown in the report footer.

Count shown in report footer and Count function shown in property box

For detailed step-by-step instructions showing how to create a count in the report footer, see the following expandable section:

ShowStep-by-step: How to place a count in a report footer

To open the report in Design view    

  1. In the Database window, under Objects, click Reports.
  2. Click the report, and then click Design in the Database window.

To create the text box control    

  1. On the View menu, select Report Header/Footer. Make sure that it remains selected. This will ensure that the Report Header and Report Footer sections appear in the report design.
  2. In the Toolbox, click the Text Box tool.

 Note   If the toolbox is not visible, on the View menu, click Toolbox.

  1. Drag the pointer into the Report Footer section to create the text box. If a label appears next to the text box, delete the label.

To set the ControlSource property of the text box    

  1. Right-click the text box, and then click Properties on the shortcut menu.
  2. Click the Data tab.
  3. To change the value of the ControlSource property, in the Control Source property box, type =Count(*).
  4. Close the property sheet.
  5. On the File menu, click Save and then close the report.

For detailed step-by-step instructions showing how to create a count in the group footer, see the following expandable section:

ShowStep-by-step: How to place a count in a group footer

To open the report in Design view    

  1. In the Database window, under Objects, click Reports.
  2. Click the report, and then click Design in the Database window.

To view the group footer    

  1. On the View menu, click Sorting and Grouping. When the Sorting and Grouping dialog box appears, click the field or expression for the group whose group footer you wish to view, then set the GroupFooter property to Yes. Note that the Group Footer section appears in the Design window.
  2. Close the Sorting and Grouping dialog box.

To create a new group level    

  1. On the View menu, click Sorting and Grouping. When the Sorting and Grouping dialog box appears, click the Field/Expression column in the first available empty row, then select the field or expression you wish to group on. Next, set the GroupFooter property to Yes. Note that the new group footer section appears in the Design window. Its caption is derived from the name of the field or expression the group level is based on — for example, if you are grouping on the ProductName field, it says "ProductName Footer."
  2. To reorder the group levels, click and drag the row selector for the selected group level up or down the list.
  3. Close the Sorting and Grouping dialog box.

To create the text box control    

  1. In the Toolbox, click the Text Box tool.

 Note   If the toolbox is not visible, on the View menu, click Toolbox.

  1. Drag the pointer into the Group Footer section to create the text box. If a label appears next to the text box, delete the label.

To set the ControlSource property of the text box    

  1. Right-click the text box, and then click Properties on the shortcut menu.
  2. Click the Data tab.
  3. To change the value of the ControlSource property, in the Control Source property box, type =Count(*).
  4. Close the property sheet.
  5. On the File menu, click Save and then close the report.

You can also use an alternate form that specifies the field whose values you want to count. For example, the following expression counts the number of values in the OrderId column:

=Count([OrderId])

When you specify a field this way, the resulting count does not include rows where the field's value is unknown (a null value), so you should usually choose a field that always has a value. In this case, the OrderId field is a good choice because it's a required field that will always have a value.

You should use the field name argument sparingly, however. Passing the Count function an asterisk within parentheses is the preferred way to count the rows, because Access recognizes this special format of the Count expression, and has built-in optimizations that let it obtain the result more quickly.

Working with missing information

One common source of confusion regarding counting and, for that matter, averaging, is how Access handles missing information. Suppose, for instance, that you want to record the cost for a new product you intend to sell, but you don't have accurate cost information yet. A database management system that doesn't support some way of marking information as missing might force you to record the cost as zero until such time as the actual cost becomes available. This would be inaccurate, of course, because your cost is not zero (assuming you're a law abiding citizen). For a period of time, your cost is simply unknown.

A null, therefore, represents only the absence of information. It does not represent a zero, or any value at all; it represents a missing value. Access supports this idea of missing information because the concept is vital to the integrity of a database. In the real world, information is often missing, even if only temporarily. Therefore, a database that models a real world entity such as a business must be able to record information as missing. Of course, you may choose not to record rows until all of the vital information is available. Access supports this by providing the ability to define a field as "Required." When a value for a field is required, it cannot contain a null value, and you cannot save the record until you enter a valid value.

The Required property for a field set to Yes

So, how does this impact the Count function? If you choose to count the values in a field that allows null values, those rows that contain a null value are not counted. They are considered missing, but not zero. This can lead to unexpected results if you, for example, use the value returned from such a count as part of an average calculation. For example, if you use Count([ItemCost]) and your table has 108 records, but 18 of them contain null values, Access includes only the 90 records that don't contain a null value.

When you pass the Count function an asterisk within the parentheses, rows are counted even if they contain null values.

What to do when there are no records

If you're starting to sell a new product, there's likely to be a period of time (hopefully short) where you haven't sold any yet. You should therefore consider the possibility that your report might have nothing to report — that is, no detail records, and the Count function might have nothing to count. In order to gracefully handle such an occurrence, you can add a few lines of carefully placed Microsoft Visual Basic® for Applications (VBA) code to your report. VBA is the programming language that Access uses.

You add the VBA code to the report's NoData event procedure (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.). Access triggers the NoData event (event: An action recognized by an object, such as a mouse click or key press, for which you can define a response. An event can be caused by a user action or a Visual Basic statement, or it can be triggered by the system.) whenever a report is found to have no records. You can place code in the event procedure to display an appropriate message and cancel the printing of the report. The code will run whenever the report runs without any records.

To create the code, take the following steps:

To open the report in Design view    

  1. In the Database window, under Objects, click Reports.
  2. Click the report, and then click Design in the Database window.

To edit the NoData event procedure    

  1. On the Edit menu, click Select Report.
  2. On the View menu, click Properties. Then select the Event tab.
  3. Click the On No Data property box. Then, click the ellipsis button (...) when it appears.
  4. When the Choose Builder dialog box appears, click Code Builder and then click OK.
  5. Enter the following code in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.) so that the Report_NoData procedure appears exactly like this when you're done:
Private Sub Report_NoData(Cancel As Integer)
  MsgBox "There are no records to report", _
          vbExclamation, _
          "No Records"
  Cancel = True
End Sub

When you're finished, on the File menu, click Save. Then, on the File menu, click Close and Return to Microsoft Office Access. Finally, close all windows except the Database window. Now when you run the report and there's no data, you'll see a message like this:.

Message box that appears during NoData event procedure

The Report_NoData procedure uses the MsgBox function to display a "There are no records to report" message and an OK button. When you click OK, the Cancel=True line in the procedure tells Access to cancel the report. Cancel is an argument that's automatically passed to the event procedure and is automatically checked by Access when the event procedure completes.

Placing a count in a page header or footer

You might want to place a count in your report at the bottom of every page rather than per group or per report. You cannot use the SQL aggregate functions in a page header or footer, but as we discussed in the article Summing in reports, you can create page totals by writing some VBA code.

Count shown in page footer of report

To do this, at the top of the page in the Page Header section, you first initialize a counter by setting it to zero, in preparation for accumulating the count. Then, you increment the counter in the Print event in the Detail section, so that a value of 1 is added for each detail row in the Detail section. Finally, you show the counter in a text box in the page footer. When the next page prints, the process is repeated. The code looks like this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  If PrintCount = 1 Then
    txtPageCount = txtPageCount + 1
  End If
End Sub

Private Sub PageHeaderSection_Print(Cancel As Integer, _
                                    PrintCount As Integer)
  txtPageCount = 0
End Sub

For detailed step-by-step instructions for creating a count at the bottom of each page, see the following expandable section:

ShowStep-by-step: How to place a count at the bottom of each page in a report

To open the report in Design view    

  1. In the Database window, under Objects, click Reports.
  2. Click the report, and then click Design in the Database window.

To create the text box controls    

  1. On the View menu, click Page Header/Footer. Make sure that it remains selected. This will ensure that the Page Header and Page Footer sections appear in the report design.
  2. In the Toolbox, click the Text Box tool.

 Note   If the Toolbox is not visible, on the View menu, click Toolbox.

  1. Drag the pointer into the Page Footer section to create a text box. If a label appears next to the text box, delete the label.
  2. Select the text box, and then click Properties on the View menu.
  3. Click the All tab, and then type txtPageCount in the Name property box.
  4. Close the property sheet.

To set the page header Print property    

  1. Double-click the page header border. The property sheet appears.
  2. Make sure that the title bar of the property sheet includes "PageHeaderSection." If it does not, click the All tab, click the Name property box, click the arrow that appears, and then click PageHeaderSection in the list.
  3. Click the All tab, click the On Print property box, and then click the ellipsis button (...) that appears.
  4. In the Choose Builder dialog box, click Code Builder, and then click OK.
  5. Edit the PageHeaderSection_Print procedure so that it appears exactly as shown here:
Private Sub PageHeaderSection_Print(Cancel As Integer, _
                                        PrintCount As Integer)
  txtPageCount = 0
End Sub
  1. On the File menu, click Close and Return to Microsoft Office Access.
  2. Close the property sheet.

To set the Detail section Print property    

  1. Double-click the border of the Detail section. The property sheet appears.
  2. Make sure that the title bar of the property sheet includes "Detail." If it does not, click the All tab, click the Name property box, click the arrow that appears, and then click Detail in the list.
  3. Click the All tab, click the On Print property box, and then click the ellipsis button (...) that appears.
  4. In the Choose Builder dialog box, click Code Builder, and then click OK.
  5. Edit the Detail_Print procedure so that it appears exactly as shown here:
Private Sub Detail_Print(Cancel As Integer, _
                             PrintCount As Integer)
  If PrintCount = 1 Then
    txtPageCount = txtPageCount + 1
  End If
End Sub
  1. On the File menu, click Close and Return to Microsoft Office Access.
  2. Close the property sheet.
  3. On the File menu, click Save and then close the report.

Counting (numbering) the items

Sometimes you want to number the items in your report. For instance, in the Sales by product report, you might want "1" to precede the first item in a product group, "2" to precede the second item, and so on. When the next product group begins, the counting starts over, and "1" precedes the first item. I discussed a technique for doing this in the "Summing in reports" article, in the section Tip: Numbering the rows. Because it's a common request related to counting in reports, I'll also revisit it briefly here.

Numbering the items in a report

You can number the items in your report by using a calculated control and the RunningSum property. First, you create a text box and set its ControlSource property to =1.

Then, you set the RunningSum property for the text box. If you want the numbering to start over for each group, set the property to Over Group. If you want to accumulate a running sum for the entire report, set the property to Over All. For more information about running sums and the RunningSum property, see the section Calculating running sums (cumulative totals), in the "Summing in reports" article.

The expression sets the value of the text box control to "1". Because the RunningSum property is used to accumulate the value, the value in the text box is increased by one for every row. The Sales by Product with Running Sum and Numbered Rows report, in the sample database, demonstrates this technique. Entering a period (.) in the Format property box appends a period to the end of the number.

For detailed step-by-step instructions for numbering the items, see the following expandable section:

ShowStep-by-step: How to number the items in a report

To open the report in Design view    

  1. In the Database window, under Objects, click Reports.
  2. Click the report, and then click Design in the Database window.

To create the text box control    

  1. In the Detail section, click where you want the number to appear.
  2. In the Toolbox, click the Text Box tool.

 Note   If the Toolbox is not visible, on the View menu, click Toolbox.

  1. In the Detail section of the report, drag the pointer to create a text box, making sure that it's wide enough to accommodate the largest item number. For example, if you will likely have one hundred orders, you will need space for at least three characters (100). If a label appears next to the text box, delete the label.
  2. Select the text box, and then click Properties on the View menu.
  3. Click the All tab, and then type txtItemNumber in the Name property box.
  4. Click the Data tab, and then select Over Group in the Running Sum property box.
  5. Type =1 in the Control Source property box.
  6. Click the Format tab, and then type #. in the Format property box.
  7. Close the property sheet.
  8. On the File menu, click Save and then close the report.

For more information

  • For information about summing in reports, see the Summing in reports article.
  • For an excellent guide to Structured Query Language, see Bowman, Judith; Emerson, Sandra; Darnovsky, Marcy. The Practical SQL Handbook: 4th Edition. Pearson Education., 2001.
  • For a varied and extremely useful collection of solutions and suggestions for Access, see Getz, Ken; Litwin, Paul; Baron, Andy. Access Cookbook. O'Reilly Media, Inc., 2003.
  • For an excellent, technical treatment of many topics concerning queries and reports, see Getz, Ken; Litwin, Paul; Gunderloy, Mike. Access 2002 Desktop Developer's Handbook. SYBEX, Inc., 2001.
 
 
Applies to:
Access 2003