• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Adding Indexes

The more data you include in your tables, the more you need indexes to help Microsoft Access search your data efficiently. An index is simply an internal table that contains two columns: the value in the field or fields being indexed and the physical location of each record in your table that contains that value. Access uses an index similarly to how you use the index in this book—you find the term you want and jump directly to the pages containing that term. You don’t have to leaf through all the pages to find the information you want.

Let’s assume that you often search your Contacts table by city. Without an index, when you ask Access to find all the Contacts who live in the city of Chicago, Access has to search every record in your table. This search is fast if your table includes only a few contacts but very slow if the table contains thousands of contact records collected over many years. If you create an index on the HomeCity field, Access can use the index to find more rapidly the records for the contacts in the city you specify.

Single Field Indexes

Most of the indexes you’ll need to define will probably contain the values from only a single field. Access uses this type of index to help narrow down the number of records it has to search whenever you provide search criteria on the field—for example, HomeCity = Chicago or HomePostalCode = 60633. If you have defined indexes for multiple fields and provided search criteria for more than one of the fields, Access uses the indexes together (using a technology called Rushmore from Microsoft FoxPro) to find the rows you want quickly. For example, if you have created one index on HomeCity and another on LastName and you ask for HomeCity = Austin and LastName = Viescas, Access uses the entries in the HomeCity index that equal Austin and matches those with the entries in the LastName index that equal Viescas. The result is a small set of pointers to the records that match both criteria.

Creating an index on a single field in a table is easy. Open the Contacts table in Design view, and select the field for which you want an index—in this case, WorkStateOrProvince. Click the Indexed property box in the lower part of the Table window, and then click the down arrow to open the list of choices, as shown in Figure 4-34.

Figure 4-34. Using the Indexed property box to set an index on a single field.

When you create a table from scratch (as you did earlier in this chapter for the Companies table), the default Indexed property setting for all fields except the primary key is No. If you use the Table Wizard to create a table (as you did for the Contacts table in this chapter), the wizard indexes fields that might benefit from an index. If you followed along earlier using the Table Wizard to build the Contacts table, you will find that the wizard built indexes for the EmailName, LastName, WorkPostalCode, and HomePostalCode fields.

If you want to set an index for a field, Access offers two possible Yes choices. In most cases, a given field will have multiple records with the same value—perhaps you have multiple contacts in a particular state or province or multiple products in the same product category. You should select Yes (Duplicates OK) to create an index for this type of field. By selecting Yes (No Duplicates) you can use Access to enforce unique values in any field by creating an index that doesn’t allow duplicates. Access always defines the primary key index with no duplicates because, as you learned in Chapter 3, all primary key values must be unique.


You cannot define an index using an OLE Object field.

Multiple-Field Indexes

If you often provide multiple criteria in searches against large tables, you might want to consider creating a few multiple-field indexes. This helps Access narrow the search quickly without having to match values from two separate indexes. For example, suppose you often perform a search for contacts by last name, first name, and middle name. If you create an index that includes all these fields, Access can satisfy your query more rapidly.

To create a multiple-field index, you must open the Table window in Design view and open the Indexes window by clicking the Indexes button on the toolbar or by choosing the Indexes command from the View menu. You can see the primary key index and the index that you defined on the WorkStateOrProvince field in the previous section as well as the indexes defined by the Table Wizard. Each of these indexes comprises exactly one field.

To create a multiple-field index, move the cursor to an empty row in the Indexes window and type a unique name. In this example, you want a multiple-field index using the LastName, FirstName, and MiddleName fields, so FullName might be a reasonable index name. Select the LastName field in the Field Name column of this row. To add the other fields, skip down to the next row and select another field without typing a new index name. When you’re done, your Indexes window should look like the one shown in Figure 4-35.

Figure 4-35. The FullName index includes the LastName, FirstName, and MiddleName fields.


To insert a row in the middle of the list in the Indexes window, right-click in the Index Name column and then choose Insert Rows from the shortcut menu. Do not click the Insert Rows button on the main toolbar—that inserts rows into the main table design.

You can remove an existing single-field index by changing the Indexed property of a field to No. The only way to remove a multiple-field index is via the Indexes window. To remove a multiple-field index, highlight the rows (by holding down the Ctrl key as you click each row selector) that define the index and then press the Delete key. Access saves any index changes you make when you save the table definition.

Access can use a multiple-field index in a search even if you don’t provide search values for all the fields, as long as you provide search criteria for consecutive fields starting with the first field. Therefore, with the FullName multiple-field index shown in Figure 4-35, you can search for last name; for last name and first name; or for last name, first name, and middle name. There’s one additional limitation on when Access can use multiple-field indexes: only the last search criterion you supply can be an inequality, such as >, >=, <, or <=. In other words, Access can use the index shown in Figure 4-35 when you specify searches such as these:

LastName = "Smith"
LastName > "Franklin"
LastName = "Buchanan" And FirstName = "Steven"
LastName = "Viescas" And FirstName >= "Bobby"

But Access will not use the FullName index shown in Figure 4-35 if you ask for

LastName > "Davolio" And FirstName > "John"

because only the last field in the search string (FirstName) can be an inequality. Access also will not use this index if you ask for

FirstName = "John"

because the first field of the multiple-field index (LastName) is missing from the search criterion.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint