Locked lesson.

Upgrade

About this lesson

Learn how to add grouping and sorting to a report to order information.

Exercise files

Download this lesson’s related exercise files.

Services_24_Start Group and Sort.accdb
1.7 MB Services_24_Group and Sort.accdb
1.7 MB

Quick reference

Group and Sort

Application Terminology

Group

In a Report, fields can have up to 10 levels of sorting and grouping.  A grouped field can have subtotals in the group header and the group footer. All records in the grouped section have the same value in the field that is used for grouping.

Group Footer

Controls in a Group Footer are displayed at the bottom of a grouped field value, just before the next Group Header.

Group Header

Controls in a Group Header are displayed each time the value in a grouped field for that section changes.

Grouped Report

A Grouped reports shows data divided into sections by field values.  For instance, you may want all customer information to appear together.  This difference between sorting and grouping is that groups can have sections for group header and group footer.

Grouping

Grouping means breaking your data at logical places, such as when the year of a date changes, usually to calculate subtotals and other statistics.

Sort

Sort means to arrange data in a particular order.  On a report, there can be 10 levels of sorting and/or grouping.  Fields can be sorted in Ascending or Descending order.

 

Steps

Add Grouping and/or Sorting to a Report

  1. Go to the Design or Layout View of a Report
  2. Click the Group & Sort icon on the DESIGN ribbon.

     
  3. Modify information in the Group, Sort, and Total pane.

Change the Background Color of a Section

  1. To change the Background color of a section, click on the section bar or a blank area of the section to select it.
  2. Click the paint bucket icon on the HOME ribbon or the FORMAT ribbon (Shape Fill)

    and choose the color
  3. or set the Back Color property on the Format tab of the Property Sheet.

Close Extra Space to the Right or at the Bottom of a Report

  1. If there are blank pages, there is extra space in the report.  Go to the Design View
  2. Drag the right border to the left when the mouse cursor shape changes to a vertical line and double-headed horizontal arrow.
  3. Drag the bottom border of each section up when the mouse cursor shape changes to a horizontal line and double-headed vertical arrow.
Login to download
  • 00:05 This is Lesson 24 of Access 2013.
  • 00:09 Sorting is ordering information by a particular field, or fields.
  • 00:15 Grouping does a sort and also gives you the option for
  • 00:19 showing header and/or footer sections for the group,
  • 00:22 along with other choices
  • 00:25 Access reports on the desktop can define up to
  • 00:28 ten levels of sorting and grouping.
  • 00:31 Hi, this is Crystal.
  • 00:34 In the last lesson, we began creating a report
  • 00:36 showing the services performed for each customer by year.
  • 00:40 In this lesson, we will continue designing this report
  • 00:44 to add grouping and sorting.
  • 00:48 Looking at what was designed on paper, you can see that
  • 00:51 information should be grouped by Year,
  • 00:54 Company, then Service Type.
  • 00:57 Within each group, data will be sorted
  • 00:59 by service name and then work date.
  • 01:02 To refresh your mind on what we did in the last lesson,
  • 01:06 open the report called r_CustomerServicesByYear.
  • 01:11 Double-click its name in the Navigation Pane.
  • 01:15 Currently, we have the data that we want
  • 01:18 but there is still a lot more to do.
  • 01:21 Go to the design view of the report and maximize.
  • 01:25 On the REPORT DESIGN TOOLS, DESIGN ribbon,
  • 01:29 click the Group & Sort icon in the Grouping & Totals group.
  • 01:34 The Group, Sort, and Total pane appears below the report.
  • 01:39 Click Add a Group and choose YearWork.
  • 01:42 Add additional groups for Customer and ServType.
  • 01:47 Below that, add a sort for ServName.
  • 01:51 Notice you can change sorting from Ascending,
  • 01:55 which is A on top, or smallest to largest.
  • 01:59 You can also sort in Descending order,
  • 02:02 which is Z on top, or largest to smallest.
  • 02:07 To modify the properties of each group or sort,
  • 02:10 click on it, and then click More to see all the options.
  • 02:14 For the Year and Company groups,
  • 02:16 we want to see a footer section.
  • 02:19 We also want to
  • 02:21 "keep header and first record together on one page"
  • 02:24 to avoid orphan information at the bottom of a page.
  • 02:29 For ServType, we will set
  • 02:31 "keep header and first record together on one page"
  • 02:36 Move the YearWork control to the YearWork Header,
  • 02:40 the Customer control into the Customer Header,
  • 02:43 and ServType to the ServType Header.
  • 02:46 If Rulers are not showing,
  • 02:48 right-click on a blank area of the report
  • 02:51 to toggle the display of Ruler.
  • 02:55 When you move your mouse into the vertical Ruler area,
  • 02:58 the cursor shape changes to an arrow pointing to the right.
  • 03:03 When you click, an imaginary line extends across, and
  • 03:06 any control that is touched will be selected when you let go.
  • 03:11 Select the detail controls and move them up.
  • 03:14 Close the extra space in the detail section
  • 03:17 by dragging the bottom border up.
  • 03:20 Now lets do some formatting.
  • 03:23 If you collapsed the ribbon, double-click on any ribbon tab
  • 03:26 to expand it, and keep it showing.
  • 03:29 Make YearWork, Customer, and ServType bold
  • 03:33 by selecting them and clicking B in
  • 03:35 the Text Formatting group on the Home ribbon.
  • 03:39 To center service type,
  • 03:40 drag its right sizing handle to the right to 7".
  • 03:45 As you drag, notice the horizontal ruler at the top
  • 03:48 shows you where you are.
  • 03:50 Click the Center icon in the Text Formatting group
  • 03:53 of the Home ribbon.
  • 03:55 To change the background color, click the paint bucket icon.
  • 03:59 Click Save on the Quick Access Toolbar,
  • 04:02 then look at the Print Preview.
  • 04:05 We still get the error message about the width being too wide
  • 04:08 because we haven't fixed that yet.
  • 04:12 Now, instead of year, customer, and service type
  • 04:15 being repeated on every record,
  • 04:17 they are shown just once at the top of each group.
  • 04:22 As you scroll through the report, you see grouped information
  • 04:24 is reported each time it changes.
  • 04:29 After service, data should be sorted by work date.
  • 04:33 The controls in the lower left
  • 04:35 enable you to move from page to page.
  • 04:38 When you click on the next button to go to page 2,
  • 04:42 you see a blank page because the report width
  • 04:45 still needs to be changed.
  • 04:47 Go back to Design View and add another sort.
  • 04:51 The choice to add a sort is not showing
  • 04:54 Use the scrollbar on the right to scroll down.
  • 04:58 Click "Add a sort" and choose WorkDate.
  • 05:02 Close the extra space on the right by dragging the right border.
  • 05:08 Go back to Print Preview.
  • 05:10 We no longer get the error message about
  • 05:12 the page being too wide, but we still have a blank page.
  • 05:17 This is not due to the width.
  • 05:20 Go back to the Design View.
  • 05:22 Notice that there are footer sections with nothing but space.
  • 05:27 We are also missing records.
  • 05:29 In the next lesson, we will learn how to modify
  • 05:32 the relationship properties in the RecordSource
  • 05:35 to show all the records

Lesson notes are only available for subscribers.

Gift this course