Enhance grouping in your Access reports

Grouping related customer records in your Microsoft Access reports allow you to look at data from different angles. Discover a quick way to put these groups in sections and then enhance these sections by providing summary details about the group.

When you're analyzing customer records for trends, grouping related records allows you to look at data from different angles. For instance, grouping sales by fields such as sales rep, state, city, or ZIP code lets you evaluate sales by region. Grouping sales by date lets you view results over time.

To group records in a report, open the report in Design view, and click the Sorting And Grouping icon on the Report Design toolbar. Choose the primary field for sorting and grouping.

The default for the Group Header and Group Footer properties is No. You don't have to define a group header or footer. If you don't activate them, Microsoft Access will sort your records by the field you select. Having the records in the right order suffices for most reports.

When you want to draw special attention to groups of records, activate either or both of the Group Header and Group Footer options. When Access adds those sections to the report design, you can add whatever you like. One way to enhance these sections is to provide summary details about the group.

For example, display the number of items in the group. Create a text box in the Group Header or Group Footer. For the Control Source property, enter the following expression:


Of course, you can label that output by concatenating a string in an expression such as:

="Total Customers This Region "&Count(*)

To determine whether to use a singular or plural noun, use the Immediate-If function:

="This region has "&Iif(Count(*)>1,"customers","customer")

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.