Tables & Queries:
Indexes

Two of the important features of a database are searching and sorting. How happy you are with your database depends heavily on how quickly such searches and sorts can be done. To speed up searches and sorts you need to tell Access to create the correct indexes.

What is an index?

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!

Index Property

In Table Design View, one of the properties for a field is called Indexed. There are three choices:

• No - The default choice
• Yes- Duplicates OK
• Yes - No Duplicates.

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.

• 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  = it 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.

Index with Multiple Fields

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 open.

View the Indexes

1. If necessary, open the Staff table to Design View.
    

2. Click on the Indexes button on the toolbar.
   Problem: Indexes button is not on toolbar:
   You are not in Design view. Change to Table Design View.
   
   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, the table wizard made some guesses as to what fields you would probably want to search or sort on.

---

Create Multiple Field Index

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. The wizard got you started.

1. In the Indexes window, change the name of the second index from LastName to just Name.
    

2. Right click on the row below and select  Insert Rows  from the menu that appears.
   A new blank row appears above the selected row, PostalCode.
    

3. Repeat so that you have two blank rows under Name.
    

4. For the first blank row, in the Field Name column, select FirstName from the drop list of fields.

   Leave the Index Name column blank.
   Leave the Sort Order as Ascending, the default choice.
    

5. For the row below FirstName, select MiddleName and leave the first column blank.
    
   The index Name now uses all three fields.
    

---

Add an Index

It seems likely that you will want to sort the staff by country. You can add an index to help.

1. In a blank row, type Country as the Index Name.
    

2. Select the field Country/Region and use the default sort order, Ascending.
    

3. Close the Indexes window.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012