MY CART
One great feature of Access is the ability to create indexes from tables. An index can help users to find and sort records faster. The index stores the location of records based on the field you choose to index. Access obtains the location from the index, and then retrieves the data by moving directly to the correct location. This can save users time.
Introduction to an Index
Indexing a table is simply a way of organizing the data in the table to allow Access to complete query searches and sorting more rapidly. Indexing can help speed up the time that it takes to complete queries in Access, given a few criteria are met first. First of all, you should only index tables that have a variety of different data types within their fields. Second, indexing is more efficient if the data in your indexed fields give each record a more unique identification (like a primary key field). Indexing is not usually necessary on fields that have multiple repeating values. Third, you really only need to index fields which are used for criteria in queries. For example, if you are creating many queries that find records based on phone numbers, you may want to create an index on the field which contains the phone numbers. Assuming your table fields have met these criteria, it can be useful to apply an index to the desired fields to increase the sorting and processing capabilities of data in queries.
Unfortunately, if you apply an index to a table that contains multiple similar data types, or has multiple duplicate information in each indexed field, you may actually slow down the speed at which Access updates table information. In these cases, it is actually preferable to not index these fields, as indexing these types of fields will rob you of more time than the time saved by using the indexed fields in the query.
Also, Access decides when and if it will choose to use the indexes that you create. All that you can do as the user is simply create the indexes for the table fields. Access will decide when and if to use the indexes when performing a query. Many times, when a query is performed on a table which has very little information, Access will perform a “table scan” on the table records, looking at all of the data in all of the fields and then extracting the requested results. However, as you add more and more data to the tables, Access may find that it is easier to index (sort) the table by one of the available indexes first, and then extract the requested records. However, it cannot do this if there are no indexes for it to take advantage of.
To view the indexes that have been created for a table, simply open up the table in design view. Next, click the “Indexes” button in the “Show/Hide” group on the “Design” tab of the “Table Tools” contextual tab to view the indexes for your selected table in a separate “Indexes” dialog box. Almost every table will have at least one index: the primary key. The primary key is a type of index. Notice that when you open a table in datasheet view, it sorts the view by the values in the primary key column, by default.
Creating Indexes
When you are creating indexes, you want to try and use field values that will identify each record in your table as uniquely as possible. If you are a good database designer, there will already be a single field in your table that already does this: your primary key field. However, you can create additional indexes on other fields to use in queries for faster query processing.
To create an index, open up the table which you would like to index in table design view. Then click the “Indexes” button in the “Show/Hide” group on the “Design” tab of the “Table Tools” contextual tab to show the “Indexes” dialog box. Click into the next available row under the “Index Name” column, and type a name for your new index. To the right, click into the “Field Name” column, and select the name of the field within the table which you wish to index. To the right of that, select whether that field should be sorted in “Ascending” (A-Z, 1-9) or “Descending” (Z-A, 9-1) order.
In the “Index Properties” section at the bottom of the dialog box, you have three drop-down text boxes into which you can set the properties of the index. The first property is “Primary,” and can accept either a “Yes” or “No” value. Whatever index is the primary key of the table will show “Yes” in this property, and all others will show “No.” There can only be one primary key field in a table.
The next property, “Unique,” asks if the values within the field will always be unique (like the values in a primary key field are). Once again, you can select either “Yes” or “No,” as appropriate.
You can then also set the “Ignore Nulls” property to “Yes” or “No” to either include or exclude “Null” (empty) values from the sorting. Nulls occur when there has been no data entry in the field for a record. For example, if you skipped entering an address into a customer record, the address field would contain a “null” value. It is not equivalent to zero, as zero is still a value. “Null” is simply “unknown.”
When you have finished creating the index, you can close the “Indexes” dialog box. Then click the “Save” button in the Quick Access toolbar to save your structural modifications to the table.
