Sorting Table Data

A fundamental requirement of a database development environment is the capability to sort records quickly so that they appear in the desired sequence. Early desktop database managers, such as dBASE II and III/III+, required that you create a new copy of a table if you physically wanted to sort the table's records in a new order. Creating and specifying an index on a field table enabled you to display or print the table in index order. If you wanted to sort the data by two or more fields, however, you had to create a composite index on the fields or presort the data in the order of one or more fields, and then apply the single-field index.

Modern desktop database development systems, such as Access, never require that you physically sort the table. Instead, the physical location of the records in the file is the order in which the records were entered. By default, Access displays records in the order of the primary key. This behavior is similar to that of Borland's Paradox. If your table doesn't have a primary key, the records display in the order you enter them. Unlike dBASE and its clones, you cannot choose a specific Access index to alter the order in which the records display in Table Datasheet view of the user interface (UI). You can, however, specify an index to order records of tables you manipulate with Access VBA code. 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 is much quicker. Access automatically uses indexes, if indexes exist, to speed the sort in a process called query optimization. Access's indexes and query optimization methods are discussed in Chapter 22, "Exploring Relational Database Design and Implementation."

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 is typical of a table whose data you can use for a variety of purposes.

Freezing Display of a Table Field

If the table you are 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. To freeze the Customer ID and Company Name fields of the Customers table, follow these steps:

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

▸▸ See "Customizable Toolbars," p. 453.

Sorting Data on a Single Field

In mailing lists, a standard practice in the United States is to sort the records in ascending ZIP Code order. This practice often is observed in other countries that use postal codes, also. To sort the Customers table in the order of the Postal Code field, follow these steps:

▸▸ See Writing Select Queries in SQL

Your Customers table quickly is sorted into the order shown in Figure 6.2. Sorting a table is equivalent to specifying the selected field as the table name of the SQL ORDER BY clause, as in

     SELECT * FROM Customers ORDER BY [Postal Code]

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 vagaries of postal-code formats used in different countries. What's needed here is a multiple-field sort first on the Country field and then the Postal Code field. Thus, you might select the Country and the Postal Code 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. Filters are discussed later in theFiltering Table Data section, so follow these steps to use the reordering process:

The sorted table, shown in Figure 6.3, now makes much more sense. Applying a multi-field sort on a table (sometimes called a composite sort) is the equivalent of the following SQL statement:

     SELECT * FROM Customers ORDER BY Country, [Postal Code]

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 to 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).

• To return to the original order when the table has no primary key field, close the table without saving the changes and 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 position 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 is usually quicker to close and reopen the table. (Don't save your changes to the table layout.)

Removing the sort order from a field is the equivalent of issuing the following SQL statement:

     SELECT * FROM Customers