XL98: How to Create an Index with Microsoft Query (193250)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q193250

SUMMARY

This article describes how to create indexes 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 OK.
  3. In the Select Table dialog box, select a table and then click Index.
  4. In the Create Index dialog box, type the name for the index in the Index Name box.
  5. In the Index Fields list, click 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.
  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.

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

188967 Query98: Indexed Field Name Does not Display in Bold Letters

Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbhowto KB193250
©2002 Microsoft Corporation. All rights reserved.