Microsoft Access 2010 Bible

Chapter 2: Creating Access Tables

81

current table. Without an index, Access has to search all records in the related table to make sure it

has located all the related records.

Tip
The performance losses due to unindexed tables can have a devastating effect on the overall performance of an
Access application. Anytime you hear a complaint about the performance of an application, consider indexing
as a possible solution.

Multiple-field indexes

Multiple-field indexes (also called composite indexes) are easy to create. In Design view, click on the

Indexes toolbar button or select the Indexes command on the View menu. The Indexes dialog box

(shown in Figure 2.28) appears, allowing you to specify the fields to include in the index.

FIGURE 2.28

Multifield (composite) indexes can enhance performance.

Primary key index

Composite index

Index name

Enter a name for the index (CityState in Figure 2.28) and tab to the Field Name column. Use

the drop-down list to select the fields to include in the index. In this example City and State

are combined as a single index. Any row appearing immediately below this row that does not con-

tain an index name is part of the composite index. Access considers both these fields when creating

the sort order on this table, speeding queries and sorting operations that include both the City

and State fields.

As many as ten fields can be included in a composite index. As long as the composite index is not

used as the table’s primary key, any of the fields in the composite index can be empty.

Figure 2.29 shows how to set the properties of an index. The cursor is placed in the row in the

Indexes dialog box containing the name of the index. Notice the three properties appearing below

the index information in the top half of the Indexes dialog box.