Working with Indexes

There's one important property we skipped over in the chapter so far: the Indexed property. That's because we wanted to discuss indexes as a separate subject. Deciding which fields to index in a table is an important part of designing a database.

An index in a database works much the same way as an index in a book. Suppose you want to find a particular topic in a book. You can leaf through every page in the book, looking for mentions of that particular topic. Or, more sensibly, you can turn to the index and use it to find the exact page where the topic is covered.

In an Access database, the indexes are not for your benefit, but for the benefit of Access. Although it might not seem like it at first, Access frequently has to look things up in the data you've entered. Suppose you create a query to display all the medicinal plants. Access can find that information by starting at the top of the Plants table and reading down, looking for plants that are medicinal. But if you add an index to the Type field in the table, Access can use that index to find the appropriate rows without searching for them.

Here's how you can add some indexes to the Plants table to make your database more efficient:

1. Open the Plants table in table design view.

2. Click in the LatinName field.

3. Set the indexed property for the field to Yes (Duplicates OK) . This tells Access to create an index on the field but to allow you to enter the same value in this field in more than one record of the table.

4. Click in the CatalogName field.

5. Set the indexed property for the field to Yes (Duplicates OK) .

6. To view all the indexes in the table, select View , Indexes or click the Indexes button on the toolbar. This opens a separate Indexes window, as shown in Figure 11.15. You can see that indexes have their own set of properties; when you're building your first databases, you should leave these properties set to their default values.

   Figure 11.15. Viewing the indexes for a table.

   

7. Close the indexes window and save the table.

If you examine Figure 11.15 closely, you'll see that this table has some indexes you didn't addAccess sometimes creates indexes on its own when it knows they will make things work better. For example, the primary key field is automatically indexed, and numeric linking fields are automatically indexed as well.

You normally shouldn't add indexes to every field of your table because indexes make saving data in the table slower. This is due to Access having to spend time updating the indexes. But remember, you usually enter data only once and then work with it many times. Here are some rules of thumb for deciding which fields need indexes:

• Add an index to any field that is used to join two tables in a relationship (Access adds most of these indexes for you).

• Add an index to any field that contains data you frequently search.

• Add an index to any field by which you frequently sort .

• Add an index set to Yes (No Duplicates) to any field where you want each record in a table to contain its own unique value. Access automatically adds such an index to the primary key field of your table.