Sorting Table Data

By default, Access displays records in the order of the primary key. If your table doesn't have a primary key, the records display in the order in which you enter them. Access uses sorting methods to display records in the desired order. If an index exists on the field in which you sort the records, the sorting process for large tables is quicker. Access automatically uses indexes, if indexes exist, to speed the sort in a process called query optimization.

The following sections show how to use Access's sorting methods to display records in the sequence you want. The Customers table of Northwind.mdb is used for most examples in this chapter because it's typical of a table whose data you might want to sort.

Note

You can use the 15MB Oakmont.mdb database, which has a 30,000-record Students table, to evaluate sorting operations on large tables. If you haven't installed all the sample files from the accompanying CD-ROM, copy Oakmont.mdb from the \Seua10\Oakmont folder to your working folder. Right-click the Explorer entry for the copy of Oakmont.mdb, choose Properties, and clear the Read-Only check box in the Attributes group.

Freezing Display of a Table Field

If the table you're sorting contains more fields than you can display in Access's Table Datasheet view, you can freeze one or more fields to make viewing the sorted data easier. Freezing a field makes the field visible at all times, regardless of which other fields you display by manipulating the horizontal scroll bar.

Note

This example and those that follow use field names, rather than column header names (captions). The Microsoft developers added spaces when two nouns make up a field name, such as Company and Name, are extended and, in this case, superficial properties of fields.

To freeze the CustomerID and CompanyName fields of the Customers table, follow these steps:

1. Open the Customers table in Datasheet view.

2. Click the field header button of the CustomerID field to select the first field.

3. Shift+click the CompanyName field header button. Alternatively, you can drag the mouse from the CustomerID field header to the CompanyName field header to select the first and second fields.

4. Choose Format, Freeze Columns.

When you scroll to fields to the right of the frozen columns, your Datasheet view of the Customers table appears as shown in Figure 7.1. A solid vertical line replaces the half-tone gridline between the frozen and thawed (selectable) fields.

Figure 7.1. The CustomerID and CompanyName fields of this Datasheet view of the Customers table are frozen.

Tip

If you frequently freeze columns, add the Freeze Columns button from the Datasheet collection to your Datasheet toolbar.

To learn how to customize your toolbars, see "Customizable Toolbars," p. 574.

Sorting Data on a Single Field

Access provides an easy way to sort data in the Datasheet view, called a Quick Sort. Simply click the field name button of the field you want to use to sort the table's data and click either the Sort Ascending or the Sort Descending icon on the toolbar. In mailing lists, a standard practice in the United States is to sort the records in ascending ZIP code order. This practice often is also observed in other countries that use postal codes. To Quick Sort the Customers table in the order of the Postal Code field, follow these steps:

1. Select the PostalCode field by clicking its field header button.

2. Click the Sort Ascending (A Z) button of the toolbar or choose Sort and then Sort Ascending from the Records menu.

   Your Customers table quickly is sorted into the order shown in Figure 7.2.

   Figure 7.2. Access's Quick Sort feature works on a single field or multiple fields in left-to-right sequence. This Datasheet is sorted on the PostalCode field.

   

Sorting Data on Multiple Fields

Although the sort operation in the preceding section accomplishes exactly what you specify, the result is less than useful because of the variants of postal-code formats used in different countries. What's needed here is a multiple-field sort: first on the Country field and then on the PostalCode field. You can select the Country and the PostalCode fields to perform the multicolumn sort. The Quick Sort technique, however, automatically applies the sorting priority to the leftmost field you select, Postal Code. Access offers two methods of handling this problem: reorder the field display or specify the sort order in a Filter window. Follow these steps to use the reordering process:

Filters are discussed later in this chapter; see "Filtering Table Data", p. 249.

1. Select the Country field by clicking its field header button.

2. Hold down the left mouse button and drag the Country field to the left of the PostalCode field. Release the left mouse button to drop the field in its new location.

3. Shift+click the header button of the PostalCode field to select the Country and PostalCode fields.

4. Click the Sort Ascending button on the toolbar or choose Sort and then Ascending from the Records menu.

The sorted table, shown in Figure 7.3, now makes much more sense. A multiple-field sort on a table sometimes is called a composite sort.

Figure 7.3. Rearrange the fields to use Quick Sort on multiple fields in left-to-right sequence. Changing the sequence of fields in Datasheet view affects the display but not the design of the table.

Removing a Table Sort Order and Thawing Columns

After you freeze columns and apply sort orders to a table, you might want to return the table to its original condition. To do so, Access offers you the following choices:

• To return the Datasheet view of an Access table with a primary key to its original sort order, select the field(s) that comprise the primary key (in the order of the primary key fields), and click the Sort Ascending button.

• To return to the original order when the table has no primary key field, close the table without saving the changes and then reopen the table.

• To thaw your frozen columns, choose Format, Unfreeze All Columns.

• To return the sequence of fields to its original state, drag the fields you moved back to their prior positions or close the table without saving your changes.

If you make substantial changes to the layout of the table and apply a sort order, it's usually quicker to close and reopen the table. (Don't save your changes to the table layout.)