Adding Indexes

The more data you include in your tables, the more you need indexes to help Access 2007 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 2007 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 2007 to find all the contacts 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 City field, Access 2007 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 the number of records it has to search whenever you provide search criteria on the field-for example, City=Chicago or PostalCode=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 City and another on LastName, and you ask for City=Bend and LastName=Conrad, Access uses the entries in the City index that equal Bend and matches those with the entries in the LastName index that equal Conrad. 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 (which you created earlier using a table template) in Design view, and select the field for which you want an index-in this case, City. Click the Indexed property box in the lower part of the Table window, and then click the arrow to open the list of choices, as shown in Figure 27–30.

Figure 27–30: You can use 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 a table template to create a table (as you did for the Contacts table in this chapter), the template indexes fields that might benefit from an index. If you followed along earlier using a table template to build the Contacts table, you will find that the template built an index only for the ContactID and Zip/Postal Code fields. Any tables created using a table template could obviously benefit from some additional indexes.

If you want to set an index for a field, Access 2007 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 city 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 have Access 2007 enforce unique values in any field by creating an index that doesn’t allow duplicates. Access 2007 always defines the primary key index with no duplicates because all primary key values must be unique.

Note 

You cannot define an index using an OLE object or attachment 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 2007 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 and first name. If you create an index that includes both of 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 in the Show/Hide group of the Design contextual tab on the Ribbon. You can see the primary key index and the index that you defined on City in the previous section as well as the index defined by the table template (Zip/Postal Code). Each of these indexes comprises exactly one field.

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

Figure 27–31: The FullName index includes the Last Name and First Name fields.

Inside Out-Inserting New Rows in the Indexes Window 

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.

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, select the rows (by holding down the Ctrl key as you click each row selector) that define the index and then press Delete. Access 2007 saves any index changes you make when you save the table definition.

Access 2007 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 27–31, you can search for last name or for last name and first 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 27–31 when you specify searches such as these:

• Last Name=“Smith”

• Last Name>“Franklin”

• Last Name=“Buchanan” And First Name=“Steven”

• Last Name=“Viescas” And First Name>=“Bobby”

But Access will not use the FullName index shown in Figure 27–31 if you ask for

• Last Name>“Davolio” And First Name>‘John”

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

• First Name=‘John”

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