Group records

Microsoft Office Access 2003

previous page next page

Open the report in Design view.
Click Sorting And Grouping on the toolbar to display the Sorting And Grouping box.
In the first row of the Field/Expression column, select the field or type the expression that you want to group records on.
The field or expression in the first row is the first grouping level (the largest set). The second row is the second grouping level, and so on.

Note When you fill in the Field/Expression column, Microsoft Access sets the Sort Order to Ascending. To change the sort order, select Descending from the Sort Order list.
Set GroupHeader or GroupFooter or both to Yes to create the group level.
Set one or more of the following group properties:
GroupOn    Specifies how you want the values grouped. The options you see depend on the data type of the field on which you're grouping. If you group on an expression, you see all the options for all data types.

GroupInterval    Specifies any interval that is valid for the values in the field or expression you're grouping on.

KeepTogether    Specifies whether Microsoft Access prints all or only part of a group on the same page.

Note The Employee Sales by Country report in the Northwind sample database has two group levels. To view this report, point to Sample Databases on the Help menu, click either Northwind Sample Database or Northwind Sample Access Project, and then open the Employee Sales by Country report in Design view.

Show On a data access page

Grouping records on a data access page is similar to grouping records on a report: You can create a hierarchy that groups records from general categories to specific details.

Show Group records by each value in a grouping field. For example, group sales by employee name.

Open the data access page in Design view.
Select the control on which you want to group records, and do one of the following:
- To group on one field from a table or query, click Promote on the toolbar.
- To group on all fields in a table or query, click Group by Table on the toolbar.
Microsoft Access adds a group header that contains an expand control and a record navigation section that contains a record navigation control. If you grouped on one field, Access moves the control you grouped on to the group header. If you grouped on a table or query, Access moves all the controls— including controls bound to fields that are in that table or query and controls bound to Lookup fields— to the group header.

Show Group records by using an expression.

Open the data access page in Design view.
If the expression will reference field names, make sure those fields have been added to the page.
Note Any field you reference in an expression must be added to the data definition of the page; the field is automatically added to the data definition when you create a control bound to that field. If you don't want to display the values in those controls in Page view or in Microsoft Internet Explorer, you can either hide or delete the controls. Even if you delete a control bound to a field used in an expression on the page, it won't be deleted from the page's data definition.
Add a bound span control for the expression by clicking Bound Span on the toolbox and then clicking in the section where you want the control.
Select the control, and then click Properties on the toolbar.
In the ControlSource property box, type an alias for the expression followed by a colon and the expression you want. In this example, FullName is the alias for the expression.
FullName: FirstName & " " & LastName
With the bound span control still selected, click Promote on the toolbar.
In the property sheet of the control, set TotalType to dscNone.
Microsoft Access adds a group header that contains an expand control, and a record navigation section that contains a record navigation control. Access moves the control on which you grouped to the group header.

Show Group records on intervals of autonumbered, currency, or numeric values. For example, group sales in intervals of 1,000.

Open the data access page in Design view.
Make sure there are two controls bound to the field you want to use to group by interval. You'll use one to group on and the other to display the actual value.
Select one of the two controls and click Promote on the toolbar.
Microsoft Access adds a group header that contains an expand control, and a record navigation section that contains a record navigation control. Access moves the control on which you grouped to the group header.
Right-click the section bar of the new group header, and then click Group Level Properties.
Set the following properties.
- Set the GroupOn property to dscInterval.
- Set the GroupInterval property to any number that's valid for the values in the field you grouped on.

Show Group records on intervals of date or time values. For example, group sales by month.

Open the data access page in Design view.
Make sure there are two controls bound to the field you want to use to group by interval. You'll use one to group on and the other to display the actual value.
Select one of the two controls, and click Promote on the toolbar.
Microsoft Access adds a group header that contains an expand control, and a record navigation section that contains a record navigation control. Access moves the control on which you grouped to the group header.
Right-click the section bar of the new group header, and then click Group Level Properties.
Set the following properties.
- Set the GroupOn property to a value in the list. You can group records on values that fall in the same calendar year, calendar quarter, month, week, day, hour, or minute.
- Set the GroupInterval property to any number that is valid for the values in the field you're grouping on. For example, if you are grouping on months, valid values for the GroupInterval property are 1 (a single month), 2 (a two-month span, such as January - February), 3 (a three-month span), 4 (a four-month span), or 6 (a six-month span).
If you want to display the value in the grouped field as an interval, do the following:
1. Add a bound span control for the expression by clicking Bound Span in the toolbox and then clicking in the group header where the control you grouped on an interval is located.
2. Select the control, and then click Properties on the toolbar.
3. Type the alias for the expression followed by a colon and the expression you want in the ControlSource property box. For example, if you grouped on ShippedDate and set the GroupOn property to dscMonth and the GroupInterval property to 1, type the following expression to display the values in the GroupOfShippedDate field as a three-letter month:
  Month: Format(GroupOfShippedDate, "mmm")
If you don't want the grouped control to be visible, double-click the control and set its Visibility property to Hidden.

Show Group records by the first n characters in a text value. For example, group products by the first letter of the product name.

Open the data access page in Design view.
Make sure there are two controls bound to the field you want to use to group by interval. You'll use one to group on and the other to display the actual value.
Select one of the two controls and click Promote on the toolbar.
Microsoft Access adds a group header that contains an expand control, and a record navigation section that contains a record navigation control. Access moves the control on which you grouped to the group header.
Right-click the section bar of the new group header, and then click Group Level Properties.
Set the following properties.
- Set the GroupOn property to dscPrefix.
- Set the GroupInterval property to the number of characters you want to group on.

Show Group records on multiple fields.

Open the data access page in Design view.
Select the control that you want to use to create the outermost group level, and click Promote on the toolbar.
Repeat step 2 for the grouping control for the next highest group level and so on, until you finish creating all the levels you want.
Set the properties for each group level.

Notes

If you add other controls to the group header, Microsoft Access groups on every control in the group header. For example, if you add a control bound to the ShipRegion field to the Orders-ShipCountry header, Access uses the values in both the ShipCountry and ShipRegion controls to group the records.
Use the Group by Table button when the page contains fields from two tables or queries that have a one-to-many relationship and you want to group on the table or query on the one side of the relationship. For example, in a page that contains fields from the Orders and Order Details tables, group on the Orders table. When you use Group By Table to group records, Access groups and sorts records by using the table's primary key— in this case, OrderID.
When you group on a control, Access creates an alias for the control by adding the prefix "GroupOf" to the control's name in the ControlSource property; for example, GroupOfShippedDate: ShippedDate. When you refer to this control in an expression or in a property, you must use the alias.
Set the DefaultSort group level property to specify a default sort order for each group level.

Show On a datasheet or form in PivotTable view

Open a datasheet or form in PivotTable view.
Do one or more of the following:
Group items at intervals
1. Select the row or column field whose items you want to group.
2. On the PivotTable toolbar, click Properties , and then click the Filter and Group tab.
3. Set Group items by to a value other than (No grouping).
4. Set Interval to the value you want. For example, if you are grouping items by prefix characters, setting Interval to 1 would group items on A, B, C, and so on.
5. To specify the value at which grouping begins, select the Start at check box and then enter a value in the text box.
6. To specify the value at which grouping ends, select the End at check box and then enter a value in the text box.
  Note The end value must be higher than the start value.
Create a custom group
1. Click the label of the parent field that contains the items you want to group, and then click Expand on the PivotTable toolbar.
2. Click the first item that you want to include in the group.
  To select more items, hold down the CTRL key and click each item.
3. Right-click a selected item, and then click Group Items.
4. Repeat steps 2 and 3 to create more groups.
Notes
- When you add the first custom group to a field, a custom group field is added to the field set. The custom group field will be the parent of the field whose items you grouped.
- You can change the caption of the custom group field and the custom groups in the Properties dialog box.

Show On a datasheet or form in PivotChart view

Open a datasheet or form in PivotChart view.
Select the category field or series field whose items you want to group.
On the PivotChart toolbar, click Properties , and then click the Filter and Group tab.
Set Group items by to a value other than (No grouping).
Set Interval to the value you want. For example, if you are grouping alphabetical items by prefix characters, setting Interval to 2 would group items based on the first two prefix characters.
To specify the value at which grouping begins or ends for numeric intervals, select either or both of the Start at or End at check boxes, and then enter a value in the adjacent text box.

previous page start next page