Ms Office Tune Up

One easy method you can use to make your Access reports more readable is to sort the records. For example, a report based on the Customers table can be sorted on the values in the CompanyName field. Here are the steps you follow to sort a report:

1. Choose Design, Group & Sort to open the Group, Sort, and Total pane.
2. Click Add a Sort. Access adds a Sort By list to the Group, Sort, and Total pane.
3. Use the Sort By list to select the name of the field on which you want to sort. Access adds a Sort Order list to the Group, Sort, and Total pane.
4. Use the Sort Order list to choose either an ascending or descending sort. Note that the name of the sort depends on the field type. For example, for text fields, you see With A on Top (an ascending sort) and With Z on Top (descending); for numeric fields, you see From Smallest to Largest (an ascending sort) and From Largest to Smallest (descending)
5. Repeat Steps 2-4 to sort the report on other fields, as needed.

For even more control over the report output, you can also create report groups. A group is a collection of related records. In a Customers report, for example, you can organize the records in groups by country. Similarly, in an invoice report you can create groups of invoices for each customer.

Why not just sort the report on the appropriate field, instead? That would work, too, but grouping gives you a big advantage over sorting: You get two new report sections-a group header and a group footer. You can use these sections to display text and calculations. For example, you can use the group header to identify the group, and you can use the group footer to print summary information about the group (such as the sum of a particular field or the total number of records in the group).

To group your report, follow these steps:

1. Choose Design, Group & Sort to open the Group, Sort, and Total pane.
2. Click Add a Group. Access adds a Group On list to the Group, Sort, and Total pane.
3. Use the Group On list to select the field you want to use as the basis of the grouping.
4. Use the Sort Order list to select a sort order for the groups. (Note that this doesn’t sort the records within the group. If you want sorting within each group, see Step 12.)
5. Click More to display a list of properties for the field.
6. Use the Group By list to specify how Access creates the groups:
   • To create a group for each unique value in the field, choose By Entire Value (this is the default).
   • If the field is numeric, click a predefined option, such as By 5s or By 100s. Alternatively, click Custom and then use the Interval text box to enter the numeric value to use as the group basis interval. For example, if you enter 20, the records are grouped in intervals of 20 (such as 1-20, 21-40, and so on) according to the values in the field.
   • If the field is text, choose either By First Character or By First Two Characters. Alternatively, click Custom and then use the Characters text box to enter the number of characters to use as the group basis. For example, when you enter 4, the records are grouped according to the first four letters in the field.
   • If the field contains dates or times, choose one of the various predefined group­ing options: By Day, By Week, By Month, By Quarter, or By Year. You can also click Custom and then use the By text box and list to set the number of Minutes, Hours, Days, Weeks, Months, Quarters, or Years to use as the grouping.
7. Use the Totals list to choose either With No Totals or to select the field on which you want the totals to display for each group. See “Adding Calculations to a Report,” later in this chapter, for more details.
8. Use the With Title control to specify a title, which displays in the group header section.
9. For the group header, choose either With a Header Section or Without a Header Section.
10. For the group footer, choose either With a Footer Section or Without a Footer Section.
11. Use the keep together list to specify if you want Access to keep the group header and footer with the group detail on the same page:
    • Do Not Keep Group Together On One Page-Choose this item if you don’t care about this.
    • Keep Whole Group Together On One Page-Choose this item if you want Access to display the entire group-the group header, detail (records), and group footer-on the same page. (If this isn’t possible, Access ignores this setting.)
    • Keep Header and First Record Together On One Page-Choose this item if you want Access to display the group header on the same page as the first group record. This prevents the group header from being orphaned, or displaying by itself at the bottom of a page.
12. If you want the records sorted within each group, click Add a Sort and then choose a sort field and sort order.
13. Click the Close button (X) to close the Group, Sort, and Total pane.

Figure 1 shows a completed Group, Sort, and Total pane that groups the Products by Category report on the CategoryName field. Notice that the Design view includes the group header and group footer sections. (These sections are given the names FieldName Header and FieldName Footer, where FieldName is the name of the field specified in the Group, Sort, and Total pane.)

Figure 1 Use the Group, Sort, and Total pane to sort the report records and group the records based on the values in one or more fields.

I added a Count Values calculation for the UnitsInStock field, and I activated the Show in group footer check box to place the total in the group footer (see Figure 2). Access added the =Count([CategoryName]) calculated control to the footer, and I added the explanatory text box (Number of Products:).

Figure 2 A preview of the report shown in Figure 1.

Technorati Tags: custom report groups, access 2007

Popularity: 4% [?]