How to Create an Index with Microsoft Query

MORE INFORMATION

Indexes can make queries run more quickly. To improve query performance, you can create indexes for fields that are used in joins, criteria, and sorts.

The more unique a field's entries are, the more an index for that field can improve query performance. For example, an index for a field of phone numbers will improve query performance more than an index for a field containing only Yes or No entries.

To create an index with Microsoft Query, follow these steps:

1. On the File menu, click Table Definition.
2. In the Select Data Source dialog box, select a data source and then click Use (in versions 5.0 and 7.0) or click OK (in the 97 version).
3. In the Select Table dialog box, select a table and then click Index.
4. In the Create Index dialog box, under Index Name, type a name for the index.
5. Select the field to be indexed.
   
   Depending on the ODBC driver you are using, you may be able to index on multiple fields, or index on an expression. For example, your ODBC driver may allow you to enter either of the following:

   LAST_NAME,FIRST_NAME
   
   RATE*INTEREST

6. Ensure that the Unique Index box is selected, and then click Add. Note that you receive the following message:

   Index was created successfully.

   If you receive an error message, click to clear the Unique Index check box.
7. Click OK.
8. Repeat steps 5-7 for any other fields that you want to be indexed on that table.
9. Click Close.
10. Repeat steps 3-9 for any other tables with fields that you want to be indexed.

When you run your query again, you may receive better performance.

Note that indexes can make data entry slower because the indexes are updated each time data is entered. If you find that your indexes are slowing down the data entry process, delete indexes for fields which contain few unique entries. For example, if a field of phone numbers is used in a criteria, create an index for it because there are many unique entries. But if 10 fields, each containing only a Yes or a No entry, are used in criteria, you may prefer not to create indexes for these fields because they will do little to improve query performance and may slow down data entry.