About indexing fields and records in an Access database (MDB)

An index helps Microsoft Access find and sort records faster. Access uses indexes in a table as you use an index in a book: to find data, it looks up the location of the data in the index. You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value.

Deciding which fields to index

You'll probably want to index fields you search frequently, fields you sort, or fields that you join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) to fields in other tables in queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.). However, indexes can slow down some action queries (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Navigation Pane.) such as append queries (append query: An action query that adds the records in a query's result set to the end of an existing table.), when the indexes for many fields need to be updated while performing these operations.

The primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) of a table is automatically indexed, and you can't index a field whose data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) is OLE Object (OLE Object data type: A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database.). For other fields, you should consider indexing a field if all the following apply:

  • The field's data type is Text, Number, Currency, or Date/Time.
  • You anticipate searching for values stored in the field.
  • You anticipate sorting values in the field.
  • You anticipate storing many different values in the field. If many of the values in the field are the same, the index may not significantly speed up queries.

Multiple-field indexes

If you think you'll often search or sort by two or more fields at a time, you can create an index for that combination of fields. For example, if you often set criteria for LastName and FirstName fields in the same query, it makes sense to create a multiple-field index on both fields.

When you sort a table by a multiple-field index, Microsoft Access sorts first by the first field defined for the index. If there are records with duplicate values in the first field, Microsoft Access sorts next by the second field defined for the index, and so on.

You can include up to 10 fields in a multiple-field index.

 
 
Applies to:
Access 2003