Multifield indexes

You can also use indexes to prevent a combination of values from being repeated. Imagine you create a People table to track your friends and their contact information. You’re likely to have entries with the same first or last name. However, you may want to prevent two records from having the same first and last name. This limitation prevents you from inadvertently adding the same person twice.

Note: This example could cause endless headaches if you honestly do have two friends who share the same first and last names. In that case, you’ll need to remove the index before you’re allowed to add the name. You should think carefully about legitimate reasons for duplication before you create any indexes.

To ensure that a combination of fields is unique, you need to create a compound index, which combines the information from more than one field. Here’s how to do it:

1. In Design view, choose Table Tools | Design -> Show/Hide -> Indexes.
   
   The Indexes window appears (Figure 4-6). Using the Indexes window, you can see your current indexes and add new ones.
   
   
   Figure 4-6.
   The Indexes window shows all the indexes that
   are defined for a table. Here, there’s a single
   index for the ID field (which Access created
   automatically) and a compound index that’s in
   the process of being created.
   
2. Choose a name for your index. Type this name into the first blank row in the Index Name column.
   
   The index name has no real importance—Access uses it to store the index in the database, but you don’t see the index name when you work with the table. Usually, you’ll use the name of one or both of the fields you’re indexing (like LastName+FirstName). 
   
3. Choose the first field in the Field Name column in the same row (like LastName).
   
   It doesn’t matter which field name you use first. Either way, the index can prevent duplicate values. However, the order does affect how searches use the index to boost performance. You’ll learn more on page 195. 
   
   Input Masks
   
4. In the area at the bottom of the window, set the Unique box to Yes.
   
   This creates an index that prevents duplicates (as opposed to one that’s used only for boosting search speeds).
   
   You can also set the Ignore Nulls box to Yes, if you want Access to allow duplicate blank values. Imagine you want to make the SSN field optional. However, if an SSN number is entered, then you want to make sure it doesn’t duplicate any other value. In this case, you should Ignore Nulls to Yes. If you set Ignore Nulls to No, then Access lets only one record have a blank SSN field, which probably isn’t the behavior you want.
   
   

   ---

   Note: You can also disallow blank values altogether using the Required property, as described on page 116.

   ---

   
   Ignore the Primary box (which identifies the index used for the primary key). 
   
5. Move down one row. Leave the Index Name column blank (which tells Access it’s still part of the previous index), but choose another field in the Field Name column (like FirstName).
   
   If you want to create a compound index with more than two fields, then just repeat this step until you’ve added all the fields you need. Figure 4-7 shows what a finished index looks like.
   
   You can now close the Indexes window.
   
   
   Figure 4-7. 
   Here’s a compound index that prevents two
   people from sharing the same first and last
   names.

Next: Input Masks >>
 

More MS SQL Server Articles
More By O'Reilly Media

Comments On: Using Masks to Block Bad Data

· This article is an excerpt from the book "Access 2007: The Missing Manual,"...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter four of Access 2007: The Missing Manual, written by Matthew MacDonald (O'Reilly, 2006; ISBN: 0596527608). Check it out today at your favorite bookstore. Buy this book now.