About Indexing an Access Database

An index helps Microsoft Access find and sort
records faster. Microsoft 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:

a.. The field's data type is Text, Number, Currency, or Date/Time.

b.. You anticipate searching for values stored in the field.

c.. You anticipate sorting values in the field.

d.. You anticipate storing many different values in the field. If many of the

svalues 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.

Create a single-field index
1.. Open a table in Design view.

2.. In the upper portion of the window, click the field that you want to create an
index for.

3.. In the lower portion of the window on the General tab, click in the Indexed
property box, and then click Yes (Duplicates OK) or Yes (No Duplicates). Click
Yes (No Duplicates) if you want to ensure that no two records have the same data
in this field.

Create a multiple-field index
4.. Open the table in Design view.

5.. Click Indexes on the toolbar.

6.. In the first blank row in the Index Name column, type a name for the index.
You can name the index after one of the index fields, or use some other
appropriate name.

7.. In the Field Name column, click the arrow and select the first field for
the index.

8.. In the next row in the Field Name column, 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. You can use
up to 10 fields. Note The default sort order is Ascending. Select Descending in
the Sort Order column of the Indexes window to sort the corresponding field's
data in descending order.

View or edit indexes
9.. Open the table in Design view.

10.. Click Indexes on the toolbar.

11.. Change indexes or index properties as desired. To delete an index, delete its
row in the Indexes window. This removes only the index, not the field itself.

Courtesy : Mr Don P. Mello(Groups.google.com)