Adding a Group and Sort to an Access Report

Adding a Group and Sort to an Access Report has the potential to make our data much easier to read.  This is because our information may appear in a clear and more logical format. Take a list of Contacts for example.  We may have a number of contacts working for the same organisation: wouldn't it be convenient for our contact list to be grouped by organisation? Then, depending on how many contacts we have within each organization, it may also make sense to sort each organizations' contact's into alphabetical order.

So instead of having a list like this:

Figure 1: Contact list without Group or Sort.

We have a list like this:

Figure 2: Contact list grouped by Company and sorted by surname and first name.

Both contain exactly the same data, but the list in figure 2 has been grouped by company and sorted by surname and first name (within each group).  You can download these reports by clicking this link: Group and Sort Sample Database.

Lets take a look at how these reports were created.

I began by creating the report from figure 1. I then opened it in DESIGN VIEW and added the Group and Sorts before re-aligning the columns.  Here are the step by step instructions:

Stage One - Create a Basic Report

1. Highlight the Contacts Table in the NAVIGATION PANE.  The table in the sample database is called tblContacts, and it is the RECORD SOURCE for both Reports.
2. Click the REPORT icon (located in the REPORTS group of the CREATE ribbon).  This is the quickest way to create a report based on a particular record source.
   
3. When the report opens in LAYOUT VIEW, click this symbol:   (it should be located at the top left corner of the ID column).  This highlights all the cells which are currently joined together. 
4. Then click the REMOVE icon to separate them.  This icon is located in the CONTROL LAYOUT group of the ARRANGE ribbon.  It will now be possible to move individual text boxes and labels independently when we go to DESIGN VIEW.  However, before we do that, we shall first add the Group and Sorts.

Stage Two - Adding the Group and Sorts

1. Select the HOME ribbon, and then pick DESIGN VIEW from the VIEWS group.
2. Make sure the GROUP, SORT AND TOTAL icon is highlighted (it is located in the GROUPING AND TOTALS group of the DESIGN ribbon).  You should see the GROUP, SORT AND TOTALS pane open below the DESIGN GRID.
3. Click ADD A GROUP from the GROUP, SORT AND TOTALS pane (see figure 3 below).
4. Then select the Company field from the list which appears.  This creates a Company Header (see figure 4 below).
5. Highlight the Company text box and make sure no other controls are highlighted.
6. Cut and past the Company text box so that it is positioned close to the left margin within the Company Header.
7. Reposition the labels and text boxes so that they are aligned in a neat logical fashion (see figure 4 below).
8. Next click ADD A SORT in the GROUP, SORT AND TOTALS pane.  
9. Then select Surname from the list.  Notice how a new level has been created below the Company Group.  This is because we want Access to apply the group first and then sort the surnames within the group.
10. Click ADD A SORT again.
11. Then select Firstname from the list. This creates another sort, but this time on Firstname.  Notice how this sort is on a level below the first sort.  This is because we want Access to begin by sorting the Surnames, and then if there are duplicates, to sort on FirstName.  This follows the general convention of placing the whole name in alphabetical order.

Figure 3: The Group, Sort and Total pane.

Figure 4: Report Design View showing a Group Header for the Company field.

You can now open the report in REPORT VIEW to see the grouped and sorted results.