Introduction to Grouping Records

Overview

When creating a table, you may have values that can be considered in categories, such as sold and unsold items, discounted and not discounted items, or males and females. When preparing a report, you can show its records by each category. This is referred to as grouping the records.

Practical Learning: Introducing Groups

1. Start Microsoft Access
2. In the list of files, click StatesStatistics2 from the previous lesson
3. To create a new report, on the Ribbon, click Create and, in the Report section, click Report Design
4. If the Property Sheet is not available, double-click the button at the intersection of the rulers.
   In the Property Sheet, click the Data tab, click Record Source, and type s to select States
5. Right-click the title bar of the report and click Save
6. Set the name to States By Region and press Enter

Creating a Group Header

To create a group of records on a report, you have various options. You can let the Report Wizard assist you or you can work manually. If you want to create the group yourself, first open the report in Design View and specify, or make sure, its Record Source has a table. Then:

• In the Design tab of the Ribbon, in the Grouping & Totals section, click the Group & Sort button or make sure it is clicked
• Right-click somewhere on the report and click Sorting and Grouping

This would add a new section titled Group, Sort, and Total under the report. At any time, to close the Grouping window, click its Close button:

One of the buttons in the new section is titled Add a Group. To create a new group, click that button. This would change the contents of the window and display a new button with a popup window that shows the fields of the table specified as the Record Source.

From the popup window, click the name of the field you want to use as the group leader. Here is an example:

This would add a new bar and section to the report above the Detail bar. The bar of the new section would display the name of the field followed by Header. Here is an example:

Also, under the report, the contents of the Group, Sort, and Total window would be changed into a new bar that displays the name of the field that was selected.

Practical Learning: Creating a Group

1. While the report is displaying in the Design View, in the Design tab of the Ribbon and in the Grouping & Totals section, make sure the Group & Sort button is clicked (if it not not, click it).

   

   Under the report, click the Add a Group button

   

2. In the window that appears, click Region

Designing a Group

After creating a group, you can add, or continue to add, the fields to the report. One of the actions you can take, but which is not required, is to add the field that holds the same name as the new section. Here is an example:

If you design the form and group that way, when the report prints, the name of the group would show before the related records: remember that the report bars do not show on the printed paper. The alternative is to put the control of the group name in the Page Header section (or another section of your choice).

After adding the field in a section, you can add, or you can continue adding, the other fields. Those other fields should be added in the Detail section or the section below the group header. If you include their labels, these labels would show for each field:

As an alternative, you can put the labels of the child fields in the section of the group header, and put the controls in the section under the group leader. Another aspect of design is that you should provide room only as much as necessary for each related record. Here is an example:

When the report displays or when it prints, all related records should show appropriately:

Still, if each record has some detail, such as a comment or a calculation you want to add, you can/should include it in the Detail section, probably under the other fields.

Practical Learning: Designing a Group

1. In the Tools section of the Ribbon, click Add Existing Fields
2. From the Field list, drag Region and drop it below the Region Header bar and to the left on the report
3. Add a line just above the Region text box
4. Add another line below the Region text box
5. In the Navigation Pane, right-click the Summary report and click Design View
6. In the Page Header section, select the line and the label except Region
7. Right-click on the selected items and click Copy
8. Return to the States By Region report
9. Right-click in the Region section and click Paste
10. Return to the Summary report
11. In the Detail section, select the line and the text boxes except Region
12. Right-click on the selected items and click Copy
13. Return to the States By Region report
14. Right-click in the Detail section and click Paste
15. Complete the design of the report as follows:

    

16. Right-click the title bar of the report and click Print Preview

    

17. Save and print the report
18. Right-click the tab of the report and click Design View

When to Display a Section

By default, after adding a section to a report, the section would show in Design View and its contents would appear in the other views. If you want, you can hide the section in either the Design View or the other views. This characteristic is controlled by the Display When enumerated property. To apply it, display the report in Design View and access the Property Sheet of the section for which you want to control this characteristic. Click either the Format or the All tab.

The Display When property has three options:

• Always: This is the default value of the property. It indicates that the section will display in Design View and its contents would appear in all views
• Print Only: The section will appear in Design View and Print Preview only, not in Report View or Layout View
• Screen Only: The section will appear in Design View, in Report View, and in Layout View, but not in Print Preview

Practical Learning: Specifying When to Display a Section

1. On the report, double-click the Region Header bar
2. In the Property Sheet, click the Format tab and click Display When.
   Notice that the Always value is selected
3. Change the value of the Display When field to Print Only
4. On the Ribbon, in the Views section, click the View button to show the Report View

   

5. In the Home page of the Ribbon, click the button below the View button and click Layout View

   

6. In the Home page of the Ribbon, click the button below the View button and click Print Preview

   

7. On the Ribbon, click the Close Print Preview button
8. In the Home page of the Ribbon, click the button below the View button and click Design View
9. In the Format tab of the Property Sheet, change the value of the Display When field to Screen Only
10. On the status bar of the report, click the Report View button

    

11. On the status bar of the report, click the Layout View button:

    

12. On the status bar of the report, click the Print Preview button:

    

13. On the Ribbon, click the Close Print Preview button
14. In the status bar of the report, click the Design View button
15. In the Format tab of the Property Sheet, change the value of the Display When field to Always
16. On the Ribbon, click File and click Open
17. From the resources that accompany these lessons, open the Altair Realtors2 database
18. To create a new report, on the Ribbon, click Create and, in the Report section, click Report Design
19. In the Property Sheet, click the Data tab, click Record Source, and type p to select Properties
20. In the Design tab of the Ribbon and in the Grouping & Totals section, make sure the Group & Sort button is clicked (if it not not, click it).
    Below the report, click the Add a Group button and click PropertyType
21. In the Controls section of the Ribbon, click the Text Box control and click in the PropertyType Header on the report
22. Change the caption of the accompanying label to Type of Property: and click the text box
23. In the Property Sheet, click the All tab and change the following properties:
    Name: PropertyType
    Control Source: PropertyType
24. Right-click the title bar of the report and click Save
25. Set the name to Properties Listing by Types and press Enter

Adding a Sub-Group to a Group

In a certain table, you may have records that can be categorized. Then, inside of each category, there may be a sub-category. When creating a report for such a table, you can create one group, then create another group inside of that group.

To create a sub-group, start the report as we learned previously. In the Group, Sort, and Total window, below the first bar, there would be a new Add a Group button and a dotted curved line joining them:

To create a sub-group, click the Add a Group button to display the list of fields and click a field from that list:

A new section would be added below the main one and it would have the same name of the field that was added, followed by Header:

Notice that below the report, a bar for the new group is added and it has its own Add a Group button, which indicates that, if necessary, you can create another sub-group. You can continue the same approach to create as many sub-sections as you judge necessary.

When designing the report, in the main section, you should add only its own field. In the group under it, you can add the field of that group and the labels of the related records if that section does not have its own sub-group. Then, in the Detail section, add the necessary controls. Here is an example:

Practical Learning: Creating Objects

1. To create a sub-group, in the Group, Sort, and Total window, click the Add a Group button below Group on Property Type and click Condition from the list
2. In the Tools section of the Ribbon, click Add Existing Fields
3. In the Field List, drag Condition and drop it in the Condition Header section on the report
4. In the Field List, double-click PropertyNumber, City, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, and MarketValue
5. Right-click each label inthe Detail section and click Cut
6. Then right-click in the Condition Header section and click Paste
7. Complete the design of the report as follows:

   

8. On the status bar of the report, click the Print Preview button

   

9. On the Ribbon, click the Close Print Preview button
10. Close the report
11. When asked whether you want to save, click Yes

Removing a Group

If you have a group you don't like on a report, you can get rid of it. To remove a group, under the report, click the Delete button that corresponds to it. You would receive a warning so you can make your final decision. If you delete a sub-group, only its section would be removed from the report. If you delete a group that has one or more sub-groups, its section and its sub-sections would be removed from the report.

Forcing a New Page

When creating a report, you add the fields of the records as you see fit in the desired sections and groups. As seen with the Keep Together Boolean property, you can exercise some control on the number of records to print on one page based on the number of fields of each record. We saw that if many records could fit on one page, then they would be grouped on that one page. In some cases, you may want each record on its own page, even if the record has only a few values, even if many records can fit on one page. Displaying each record on its own page is referred to as forcing a new page.

The ability to force each record on its own page is controlled by the Force New Page enumerated property. Its values are:

• None: This is the default value and simply specifies that the record will print on the page
• Before Section: The section or group will print in the top section of a new page and the rest of the page could be left empty. The next section or group would print on the next page:

  

  After setting the value, you can see the result in the Print Preview

  

  

  

   
  If you create a report with various groups, probably the best way to use the Before Section option is to apply it on the first or main group. This would display each record on its own page
• After Section: This section or group will print on this page. Then the section or group after this one will print in the top section of the next page
• Before & After: This section or group will print in the top section of this page. Then the section or group after this one will print in the top section of the next page

Practical Learning: Creating a Catalog

1. The AltairRealtors2 database should still be opened.
   On the Ribbon, click Create.
   In the Reports section, click Report Design
2. Using the Property Sheet, set its Record Source to Properties
3. Right-click the report and click Report Header/Footer
4. Save the report as Real Estate Catalog
5. Widen the report up to 7 and 7/8
6. Increase the height of the Report Header section down to 10.50
7. Increate the height of the Detail section down to 9.50
8. Increase the height of the Report Footer section down to 5
9. Design the report as follows:

   

   

   

10. Save the report
11. Close the report
12. In the Navigation Pane, right-click the Real Estate Catalog report and click Print Preview

    

    

    

    

13. Close Microsoft Access

Previous	Copyright © 2002-2021, FunctionX	Next