Adding Indexes

The more data you include in your tables, the more you need indexes to help Access 2010 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 2010 uses an index similarly to how you use the index-you find the term that 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 2010 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 2010 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 that you want quickly. For example, if you have created one index on City and another on LastName, and you ask for City = Redmond and LastName = Conrad, Access uses the entries in the City index that equal Redmond 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 an Application Part) 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.

When you create a table from scratch (as you did earlier in this tutorial for the Companies table), the default Indexed property setting for all fields except the primary key is No. If you use an Application Part or a Data Type Part to help create a table (as you did for the Contacts table in this tutorial), the Application Part or Data Type Part indexes fields that might benefit from an index. If you followed along earlier using an Application Part to build the Contacts table, you will find that the template built an index only for the ContactID and ZIPPostal fields. Any tables created using an Application Part or Data Type Part could obviously benefit from some additional indexes.

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

Remember You cannot define an index using an OLE Object, Attachment, or Calculated field.