How to Create a Grouped Report using the Report Wizard in Access 2016
You can create a grouped report easily using the Report Wizard in Access 2016.
A grouped report (also known as a summary report), is a report where one or more fields are used to group the other fields. This can be handy if one field has lots of repeating values, as you can display the value once, then display all records that belong to that group.
Below, we will use the Report Wizard to create a grouped report.
Launch the Report Wizard
Report Wizard on the Ribbon (from the Create tab).
Select the Fields
Select the fields that you'd like to appear on the report. You can select fields from more than one table or query.
To do this, select a table or query using the drop-down list.
Then double-click a field in the left pane to move it across to the right pane. Repeat this for each field that is to appear in the report. The fields in the right pane will appear in the report.
Repeat for each relevant table/query.
Once done, click
You can also select a field, then use the
> button to move it across to the other pane.
You can use the
>> button to move all fields across.
Choose a Grouping Table/Field
In the left pane, select the table or query that contains the field that the report needs to be grouped by.
The right pane will show a preview based on your selection.
The preview will only group the report if it finds a suitable field to group on. Otherwise, it will display the fields ungrouped.
If the preview doesn't display any grouping here, then your report will not be grouped. In this case, you'll need to go back and add a suitable field for grouping the report.
Alternatively, you can modify the report later and choose the grouping field at that time.
Add Extra Grouping if Required
Access now gives you the opportunity to specify more grouping levels if required.
In our example, we'll leave it as it is (at one grouping level).
Specify the Sort Order if Required
You can specify fields to sort the report details by. This sorts the fields within each group (not the grouped fields).
Once done, click
Choose a Layout
Select one of the preset layouts for the report.
The preview will update when you select an item. Feel free to select each one to see how it affects the layout.
You can also change the orientation to Landscape if required.
For our example, we'll leave the report with a
Stepped layout and a Portrait orientation.
Once done, click
Name the Report
Enter a name for the report.
You also have the option of either previewing the report or modifying it's design once the wizard has finished creating it.
For our example, we'll leave it at
Preview the report.
Finish to generate the report.
The report is now created. It is grouped just as we specified.
Reformatting the Report
You might need to do some light reformatting to make the report look more presentable.
In our example, we'll need to expand the width of the date field, and maybe rearrange the fields a little, and perhaps remove the borders and adjust background colors.
Access loves to put borders around fields. If you don't want the border, you can remove it.
Select the field/s you want to remove the border from.
Shape Outline from the Format tab on the Ribbon, and select Transparent from the contextual menu.
Review the Alternating Colors
Be sure to pay attention to how the alternate colors are rendered (i.e. the ones that Access automatically applies to reports).
The alternating colors on a grouped report can reduce the clarity of the grouped field. You might find that the grouped field shares its alternating color with either the preceding field, or the subsequent field. This ends up reducing that "grouped" look, and could possibly confuse the reader as to which fields are actually grouped.
Remove the Alternating Colors
To remove the alternating colors, select the report Detail section in Design View.
Alternate Row Color from the Format tab on the Ribbon, and select No Color from the contextual menu.
You can do the same thing for the grouped field. To select the grouped field, select
where FieldName Header FieldName is the name of the grouped field.
You can also use a bold typeface for the grouped field by selecting the field, then clicking the Bold icon in the Ribbon.
The Finished Report
Now, viewing the report in Print Preview (or another view) will display the report without the alternating colors.
In our example, the grouped field is also rendered in a bold typeface (after we bolded it using the Bold command in the Ribbon).