Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Tables > Indexes
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: Tables: Indexes

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.

What is an index?

Index for a book

Sample of a book's 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".

WarningWarning: Too many indexes will slow the database down! Just the opposite of what we want! Don't index every field!


Index Property

Table Design view: Indexed- choicesIn 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.

  • Indexes for the Classes/Subjects tableIndex 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.

Index with Multiple Fields

Indexes for the Students tableIf 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.
 


Icon: Step-by-Step 

Step-by-Step: Indexes

 Icon: Step-by-Step

What you will learn: to view a tables indexes
to create an index using several fields
to add an index

Start with:  Class disk, Projects database from previous lesson open.

View the Indexes

  1. Indexes for Staff tableIcon: Design View If necessary, open the Staff table to Design View.
     

  2. Click on the Indexes button Button: Indexes 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.


Create Multiple Field Index

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.

  1. Indexes for Staff table with new index using 3 fields

    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.

  2. In the next blank row, leave the Index Name blank but enter FirstName in the Field Name column.
  3. In the next blank row, leave the Index Name blank and enter MiddleName in the Field Name column.
     
    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.

  4. Button: Save (Access 2010) Close and save the Staff table.