Creating Indexed Fields

Just like an index in a book, an index in Access helps you locate and sort information quickly, especially in a very large table. An index in Access is an invisible data structure that stores the sort order of a table based on the indexed field or fields. When you sort a large table by an indexed field, Access consults the index and is able to sort the table very quickly. It can be helpful to index fields you frequently search or sort or fields you join to fields in other tables in queries. If a field contains many different values, rather than many values that are the same, indexing can significantly speed up queries. After indexing a field, you can view and then modify indexes as necessary.

Create a Field Index

Display the table in Design view, and then click a field you want as an index.

Click the Indexed box.

Click the list arrow, and then select one of the following.

• Yes (Duplicates OK) if you want to allow multiple records to have the same data in this field.

• Yes (No Duplicates) option if you want to ensure that no two records have the same data in this field.

View or Edit Indexes

Display the table in Design view.

Click the Indexes button on the Table Design toolbar.

Type a name for the index.

Select a field to act as an index.

Click the list arrow, and then select Ascending or Descending to indicate the index sort order.

Click the Close button.

Did You Know?

You can index data types . You don't have to index all data types, and there are some data types you cannot index. For example, you do not need to index the primary key of a table, because it is automatically indexed. You can index a field only if the data type is Text, Number, Currency, or Date/Time. You cannot index a field whose data type is Memo or OLE Object.

You can create a multiple-field index . If you think you'll often search or sort by two or more fields, create a multiple-field index by adding additional fields in the Field Name column for each index name.