Just like an index in a book, when you index a field, it helps Access find and sort information quickly—especially in large tables. Here are some more important notes about indexes:
- Since indexes speed up searching and sorting, you should index the fields you frequently use to search or sort. For example, if you often search for specific last names, you should create an index for the Last Name field.
- Don’t index too many of a table’s fields. The more fields you index, the slower your searches and sorts will be—defeating the entire purpose of an index. Only index the fields you use to search and sort data.
- Any field can be indexed except memo, OLE, attachment, calculated, and hyperlink fields.
- Primary key fields are indexed automatically.
- If you choose, indexes can prevent duplicate entries in your table (for example, if you don’t want to allow two customers to have the same social security number).
- Open the table you want to modify in Design View.
- Click the field name you want to index from the Field Properties section.
- Click the Indexed row and click its list arrow.
- Select an indexing option from the list.
- No: The field is not indexed. This is the default setting.
- Yes (Duplicates OK): The field is indexed and Access will allow records in this field to have the same value.
- Yes (No Duplicates): The field is indexed and Access won’t allow records in this field to have the same value, preventing unwanted duplicates.
- Save your changes.
If you need to remove an index from a field, select the field, click the Indexed row’s list arrow, and select the No option. Access will delete the field’s index.