Indexing a Field

Adding an Index to a table's LastName field.

You can index any field in a table to dramatically speed up queries and sorts. When you sort or query a large table using an indexed field, Access finds or sorts the information by consulting the index instead of sifting through the entire table.

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 LastName 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, and hyperlink fields.

• Primary key fields are indexed automatically (we'll discuss primary keys more in future lessons).

• 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).

This lesson will give you some practice adding indexes to your tables.

1. Make sure you have the tblCustomers table open in Design view.

   Indexing a field is a fairly simple operation. First you need to click the name of the field you want to index. The LastName field is a great index candidate because it is frequently used to find and sort information.

2. Click the LastName field.

   The blinking cursor should appear in the LastName field. Here's how to index the selected field.

3. In the Field Properties section, click the Indexed list arrow, as shown in figure.

   The Indexed list gives you three choices:

   • 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 (for example, if you don't want to allow two customers to have the same social security number).

   Most of the time you will want to choose the "Yes (Duplicates OK)" option, since some people may have the same last name.

4. Select the Yes (Duplicates OK) option from the list.

   Most of the time Access creates the index in a matter of seconds. If you have a huge table with thousands of records, it will take longer to create the index.

   Let's try another field. Since you do a lot of sorting by Zip Codes, let's index the ZipCode field as well.

5. Click the ZipCode field and then click the Indexed list arrow.

   Since people can (and do) live in the same Zip Code, you want to select the "Yes (Duplicates OK)" option.

6. Select the Yes (Duplicates OK) option from the list.

   You don't need to index any of the other fields in this table since you don't use them as frequently in your sorts and queries. You do need to save the changes you've made to your table, however.

7. Click the Save button on the toolbar to save the changes you've made to the table.

If you need to remove an index from a field, select the field, click the Indexed list arrow, and select the "No" option. Access will delete the field's index.

TO INDEX A FIELD:

1. Make sure the table is open in design view and then click the field you want to index.

2. click the indexed box.

3. click the list arrow and 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) if you want to ensure that no two records have the same data in this field.