MS-Access / Getting Started

---

Getting the most from your tables

The preceding sections documented many technical issues that should be reviewed to improve application speed, but sometimes it's advantageous to get back to the basics when designing your applications. Tools like Access enable novices to create relational databases quickly and easily, but they don't teach good database design techniques in the process. (An exception to this statement is the Table Analyzer Wizard. Click the ribbon's Database Tools tab, then click the Analyze Table command in the Analyze group to start the Table Analyzer Wizard.)

Caution: Even though the Table Analyzer Wizard offers suggestions that are often helpful in learning good design technique, its recommendations should never be taken as gospel. The Table Analyzer has proven to be wrong on many occasions.

Entire volumes of text have been devoted to the subject of database theory. Teaching database theory is certainly beyond the scope of this tutorial. However, you should be familiar with many basics of good database design.

Creating efficient indexes

Indexes help Access find and sort records faster and more efficiently. To find data, Access looks up the location of the data in the index and then retrieves the data from its location. You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may have the same value. If they're defined properly, multiple-field indexes can be improve the performance of queries. This is because Microsoft's Rushmore query optimization (the technology that Jet uses to optimize the speed at which queries execute) knows how to use multiple-field indexes.

Deciding which fields to index

People new to database development typically make two mistakes: First, not using indexes and, second, using too many indexes (sometimes putting an index on every field in a table). Both of these mistakes are serious. Sometimes a table with too many indexes may give slower performance than a table with no indexes. Why? When a record is saved, Access must check every index in the table, taking time and using a considerable amount of disk space. The time used is rarely noticed with a few indexes, but a lot of indexes can require a huge amounts of time for updates.

In addition, indexes can slow some action queries (such as append queries) because the indexes for updated fields need to be updated while performing the query's operations.

When you create a primary key for a table, the field (or fields) used to define the key is automatically indexed. You can index any field unless the field's data type is Memo or OLE Object. You should consider indexing a field if any of the following rules apply:

• The field's data type is Text, Number, Currency, or Date/Time.
• You anticipate searching for values stored in the field.
• You anticipate sorting records based on the values in the field.
• You will join the field to fields in other tables in queries.
• You anticipate storing many different values in the field. (If many of the values in the field are the same, the index may not significantly speed up searches or sorting.)

When defining an index, you have the option of creating an ascending or descending index. Ascending indexes are the default. But a descending index can be valuable in the case of fields such as dates, where the field may be sorted in descending order so that more recent dates appear at the top of the query's results.

Using multiple-field indexes

When frequently searching or sorting by multiple fields at the same time, you can create an index on the combined fields. For example, if you often set criteria for LastName and FirstName fields in the same query, it makes sense to create a multiple-field index on both fields.

When sorting a table by a multiple-field index, Access first sorts by the first field defined for the index. If the first field contains records with duplicate values, Access then sorts by the second field defined for the index, and so on. This creates a drill-down effect. For a multiple-field index to work, a search criterion must be defined for the first field in the index, but not for additional fields in the index. In the preceding example, if you wanted to search for someone with the last name Jones, but you didn't specify a first name to use in the search, the multi-field index wouldn't be used. If you need to perform searches on individual fields in a multiple-field index, you should create an index for each field in addition to the multiple-field index. It's not necessary to create an additional index for the first field in the multi-field index.