In Access, you use reports to print out and present records to a variety of audiences. A great way to make reports more valuable is to use grouping, a close cousin to sorting. Sorting and grouping are often used together to make data easier to find and absorb.

Let’s look at an everyday example to get a general idea of how sorting and grouping work. Suppose you want to know how the Arizona Diamondbacks, the 2001 World Series champs, are doing this year. The Diamondbacks are in the West Division of the National League. In the sports section of your newspaper, you’ll first look for the National League standings, and then its West Division; among the West Division teams, you’ll find the Diamondbacks’ record.

We can say that baseball teams are grouped first by league (National or American) and then by division (East, Central, or West). Within each division, the teams are sorted in descending order, based on their won-lost records: the team with the best record is on top, and the worst team is in the cellar.

 Sort & Group In Access

Similar sorting and grouping techniques are used in nearly all Access reports. Open the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. In the Database window, click the Reports button. Open the Products By Category report. Maximize the report using the button at the top-right corner of the window so you can view it easily.

The report shows the products in Northwind’s inventory. The products are grouped by category (Beverages, Condiments, and so on). Within each category, the products are sorted alphabetically in ascending (A to Z) order. Close the report.

In the Products By Category report, the total number of records is relatively small, and each category nicely comprises one discrete group. But let’s say you need to review sales statistics for a larger company with thousands of customers. If you group by customers, you may want to segment the group into smaller subgroups. For example, you could create subgroups based on the first two or three letters of the customer’s name. In this case, you would be using group intervals to slice up the group.

Confused? Let’s create a report using grouping, sorting, and group intervals to better understand how each of these techniques produces outstanding reports.

 Create A Report

In the Database window, click the Tables button. Double-click the Orders table to open it. Drag the horizontal scroll bar slowly to the right and review the various fields in the table. Return the scroll bar to its original position. Close the table.

The Orders table should be selected in the Database window. Click the Insert menu and choose Report. In the New Report dialog box, click Report Wizard and click OK.

In the first dialog box, you select fields for your report. Select OrderID and click the greater than (>) button to move the field from the Available Fields to the Selected Fields area. Do the same for CustomerID, EmployeeID, OrderDate, RequiredDate, and ShippedDate. Click Next.

 Select Grouping, Sorting & Layout

In the next dialog box, Access has already grouped the report by Customer ID. But suppose you want to view the records based on the date of the order. You can change the grouping to OrderDate.

First, click the less than (<) button to return the CustomerID field to the list of fields at the left. Second, choose the OrderDate field and click the greater than button. At top right, you see OrderDate by Month. The group is OrderDate, and the grouping interval is Month.

Although Access chose month as the grouping interval, you can choose a different time span. Click the Grouping Options button. Click the drop-down arrow for Grouping Intervals. You see various selections, including Year, Quarter, and Week. Click Cancel to leave the interval at month. Click Next.

You can choose to sort on as many as four fields. Let’s sort on CustomerID so that, within each month, orders of the same customer will be adjacent to one another. Open the drop-down list for the first sort and choose CustomerID. Click Next.

This dialog box asks you how you would like to lay out your report. Keep the default Stepped layout. Because we have several fields, however, let’s change the orientation from Portrait to Landscape. That way, we can fit more fields (but fewer records) on a page. Leave the Adjust The Field Width So All Fields Fit On A Page option checked. Click Next.

The next dialog box lets you choose a style. Choose the Corporate style and click Next.

In the final dialog box, at the top, type the name Smart Computing Grouping reportfor your report. Leave the Preview The Report option checked. Click Finish to create your report.

 Review Your Report

Maximize your report for easy viewing. Scroll down the report so that you view records for July 1996 and August 1996 (August will be on the second page). Your records are grouped by order date in monthly intervals. Within each group interval, the records are sorted in ascending order (A to Z) by CustomerID.

Close the report. If you wish to delete the report, click the Reports button in the Database window, select the Smart Computing Grouping report, and press the DELETE key.