Here is what the Help file in Access says about indexes:
About indexing fields and records in an Access database:
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 to fields in other tables in queries. However,
indexes can slow down some action queries such as append queries, when the
indexes for many fields need to be updated while performing these
operations.
The primary key of a table is automatically indexed, and you can't index a
field whose data type is OLE Object. 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.
