ACC97: How to Add an Index in an Access Database (304441)


The information in this article applies to:

  • Microsoft Access 97
This article was previously published under Q304441
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

An index helps Microsoft Access find and sort records faster. Access uses indexes in a table the same way that you would use an index in a book. To find data, Access looks up the location of the data in the index.

You can create indexes based on a single field or based on multiple fields. Multiple-field indexes enable you to distinguish between records that may have the same value in the first field.

MORE INFORMATION

Deciding Which Fields to Index

Fields that you should consider indexing are fields that you search frequently, fields that you sort, or fields that you join to fields in other tables.

The primary key of a table is automatically indexed, and you cannot index a field whose data type is OLE Object. For other fields, you should consider indexing a field if all the following apply:
  • The data type of the field 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.

Creating a Single-Field Index

To create a single-field index, follow these steps:
  1. Start Microsoft Access, and then open the database you are working with.
  2. Open a table in Design view.
  3. Click the field that you want to create an index for.
  4. Click in the Indexed property box, and then click Yes (Duplicates OK) or Yes (No Duplicates).

Creating Multiple-Field Index

If you will 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, you should 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 that is 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.

To create a multiple-field index, follow these steps:
  1. Open the table in Design view.
  2. On the View menu, click Indexes.
  3. Type a name for the index in the first blank row of the Index Name column. You can name the index after one of the index fields, or you can use another name.
  4. Click in the Field Name column, click the arrow, and then select the first field for the index.
  5. Click in the next row in the Field Name column, click the arrow, and then select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index.

REFERENCES

For more information about creating indexes, click Microsoft Access Help on the Help menu, type create an index in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbhowto KB304441
©2003 Microsoft Corporation. All rights reserved.