Part IV: Professional Database Development
858
l (^) The field’s data type is Text, Number, Currency, or Date/Time.
l You anticipate searching for values stored in the field.
l (^) You anticipate sorting records based on the values in the field.
l You will join the field to fields in other tables in queries.
l (^) 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.
Getting the most from your queries
The performance problems of many Access applications result from query design. Database appli-
cations are all about looking at and working with data, and queries are the heart of determining
what data to look at or work with. Queries are used to bind forms and reports, fill list boxes and
combo boxes, make new tables, and perform many other functions within an Access application.
Because they’re so widely used, optimize your queries is extremely important.
A query that is properly designed can provide results minutes to hours faster than a poorly
designed query that returns the same result set. Consider the following:
l When designing queries and tables, you should create indexes for all fields that are used
in sorts, joins, and criteria fields. Indexes enable Jet to quickly sort and search through
your database.
l (^) When possible, use a primary key in place of a regular index when creating joins. Primary
keys don’t allow nulls, and they give the query optimizer more ways to use the joins.