Working with Indexes

There's one important property we skipped over in the chapter so far: the Indexed property. That's because we wanted to discuss indexes as a separate subject. Deciding which fields to index in a table is an important part of designing a database.

An index in a database works much the same way as an index in a book. Suppose you want to find a particular topic in a book. You can leaf through every page in the book, looking for mentions of that particular topic. Or, more sensibly, you can turn to the index and use it to find the exact page where the topic is covered.

In an Access database, the indexes are not for your benefit, but for the benefit of Access. Although it might not seem like it at first, Access frequently has to look things up in the data you've entered. Suppose you create a query to display all the medicinal plants. Access can find that information by starting at the top of the Plants table and reading down, looking for plants that are medicinal. But if you add an index to the Type field in the table, Access can use that index to find the appropriate rows without searching for them.

Here's how you can add some indexes to the Plants table to make your database more efficient:

  1. Open the Plants table in table design view.

  2. Click in the LatinName field.

  3. Set the indexed property for the field to Yes (Duplicates OK) . This tells Access to create an index on the field but to allow you to enter the same value in this field in more than one record of the table.

  4. Click in the CatalogName field.

  5. Set the indexed property for the field to Yes (Duplicates OK).

  6. To view all the indexes in the table, select View, Indexes or click the Indexes button on the toolbar. This opens a separate Indexes window, as shown in Figure 11.15. You can see that indexes have their own set of properties; when you're building your first databases, you should leave these properties set to their default values.

    Figure 11.15. Viewing the indexes for a table.

    graphics/11fig15.jpg

  7. Close the indexes window and save the table.

If you examine Figure 11.15 closely, you'll see that this table has some indexes you didn't addAccess sometimes creates indexes on its own when it knows they will make things work better. For example, the primary key field is automatically indexed, and numeric linking fields are automatically indexed as well.

You normally shouldn't add indexes to every field of your table because indexes make adding or updating data in the table slower. This is due to Access having to spend time updating the indexes. But remember, you usually enter data only once and then work with it many times. Here are some rules of thumb for deciding which fields need indexes:

  • Add an index to any field that is used to join two tables in a relationship (Access adds most of these indexes for you).

  • Add an index to any field that contains data you frequently search.

  • Add an index to any field by which you frequently sort .

  • Add an index set to Yes (No Duplicates) to any field where you want each record in a table to contain its own unique value. Access automatically adds such an index to the primary key field of your table.

The Absolute Minimum

Access makes working with most objects easy through its wizards and other tools, but sometimes you just have to get under the hood and make things better. In this chapter, you learned about some of the things you can do in table design mode, including the following:

  • How to control the data that goes into a field by carefully choosing the data type for the field

  • How the caption and description properties of a field let you supply helpful information for users of your database

  • How the input mask and format properties of a field let you control what data is entered and displayed

  • How the validation rule and validation text properties of a field help you limit the data to acceptable values and how to show a friendly error message in case of any problems

  • How indexes let Access find and sort data in your database more quickly



Absolute Beginner's Guide to Microsoft Office Access 2003
Absolute Beginners Guide to Microsoft Office Access 2003
ISBN: 0789729407
EAN: 2147483647
Year: 2002
Pages: 124

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net