Two of the important features of a database are searching and sorting. How happy you are with your database depends a lot on how quickly such searches and sorts can be done. To speed things up you need to tell Access to create the correct indexes.
Index for a book
In a book, an index is the list in the back of the book that shows words and phrases with the page numbers where you can find them in the book.
An index in a database does much the same. It is a list of the various values in a particular field or in a combination of fields and which records they are in. It is faster to search the simple index table than it is to have to move whole records around in the computer's memory.
Searching and sorting a small number of records doesn't take much time no matter how the database is designed! But as your database accumulates records, your design choices can make your database a sluggish tortoise or a fast-as-lightning racing rabbit!
The plural of index is "indexes" or "indices". Microsoft uses "indexes".
Warning: Too many indexes will slow the database down!
Just the opposite of what we want! Don't index every field!
In
Table Design View, one of the properties for a field is called Indexed.
There are three choices:
You need to know what kinds of records you will be entering in the future (again!).
Examples:
- Postal codes = Duplicates OK - for sure!
- Social security numbers or national ID numbers = No Duplicates.
- Last name = Duplicates OK
- Product ID number = It depends! Duplicates OK, in a table of Orders since several customers will order the same product. No Duplicates, in a table of Products since each item should have its own ID number.
Index Window: Shows all indexes for the selected table.
Index
Name - Usually the same as the field name or based on it.
The illustration for Indexes:Classes/Subjects shows three indexes. The primary key is always indexed.
The index for the Subject field includes the table name. There is at least one other table that has a field with the same name, so Access adds the table name to keep things clear.
Field Name
Sort Order - Ascending by default. You can change the order to Descending in this window.
Properties of an Index:
Primary - No = This field is not the primary key
for the table.
Yes = This field is the primary key
Unique - No = Duplications are allowed;
Yes = No
duplications
Ignore Nulls - No = Include all records, even if
the field being indexed does not have a value.
Yes = Do not include
records where the field is empty or null.
If you expect to sort or search often on a combination of
fields, like LastName, FirstName, MiddleName, then you can create an
index based on all three fields. You can include up to 10 fields in an
index but that is not usually a good idea. Your index won't help if it includes nearly all of the fields in
the table!
To create an index for several fields, you work in the Indexes window. Name the index in the first column.
Choose the first field in the Field Name column. Drop down and pick the
second field, leaving the Index Name blank for this second field.
Continue for each of the remaining fields.
![]() |
Step-by-Step: Indexes |
![]() |
What you will learn: | to view a tables indexes to create an index using several fields to add an index |
Start with: ,
Projects database from previous lesson open.
If
necessary, open the Staff table to Design View.
Click on the Indexes button on the
ribbon tab
Table Tools: Design.
A window appears with a list of all fields in this table that have
been indexed.
The primary key is always indexed. When you created this table, Access made some guesses as
to what other fields you would probably want to search or sort on.
You can add other indexes that Access did not create itself. It seems likely that you will be sorting records on the staff members' names, LastName, FirstName, MiddleName. So it makes sense to create an index on that combination.
In the first blank row, create a new index:
Index Name = Name
Field Name = LastName
Sort Order = Ascending
Did you notice Access autofill as you typed in the Field Name box? You can even drop a list of all the fields from that box if you don't remember exactly what the fields are.
It seems likely that you will want to sort the staff by country. You can add an index to help.
In a blank row, type Country as the Index Name.
Select the field Country/Region and use the
default sort order, Ascending.
Close the Indexes window.