Indexes

When you create an index for a “table”, you determine how the data in a “table” is to be sorted.
The index of a table is simply a way to organize your data, and makes searches in queries faster by degrees.

Indees can help your queries to be faster by first performing a number of criteria.
Firstly, you can index tables as they may consist of a variety of data types in the different fields.
Secondly, indexing is more efficient when the data in your indexed fields for each record has a unique identifier (such as a primary key field). Indexing is of little use if a field is filled with repetitive information.
Thirdly, you should really only index those fields that are used as criteria in queries.

Assuming that your fields meet these criteria, then it is useful to index.
Unfortunately, if you apply an index on a table that has recurring data types used in fields, or fields that have redundant information, it will not improve the speed, but rather slow down.
In this case, it is better not to apply indexing to the fields.
Besides, “Access” will decide whether and when the indexes you have created will be useful.
All you can do is create the indexes.
Access will also decide whether and when the indexes will be useful on queries.

In many cases, when a query is executed on a table with little information, the “Access” “Table” scans the records and then displays the results.
If there is a lot of information to process, “Access” will first try to index your “Table” and then display the results. But for this to happen we need fields that are to be indexed.

To create “Indexes” in a table, we open the table in “Design View”.

Click on the “Indexes” button  on the toolbar.
or choose “View” – “Indexes” in the menu bar.

Note that almost every table has at least one index, ie the primary key

The index creation

When you create “Indexes”, you want to use field values that are as unique as possible. If you’re a good database designer, you already have such a field, the primary key.
However, you can create additional indexes on other fields to use in “Queries”, and thus achieve quicker query executions.

To create an index we open the table to index, in “Design View”.

Then click on the “indexes” icon.

Click on the next available row, and type the name for the index that you wish to create.
Right click in the “Field Name” column and select the name of the field in the table that you want indexed.
Choose “Ascending”

(“Ascending”)

Or “Descending”

(“Descending”)

How you want to sort.

In the “Index Properties” section at the bottom of the dialog, find the dropdown text fields where you can set the properties of the index.
The first is Primary and the options are Yes and No.
Only when the index is also the Primary Key of the table, this field has the “Yes” value. All others will have the value “No”.

Only one primary key is allowed in a table.
The next attribute “Unique” asks whether the value of the field is always unique. Here also you have the choice between Yes or No..
And in the last property we determine whether we would like to exclude “null” values (ie fields where nothing was entered) from the sort.

Once everything is set we close the “Indexes” dialog box and click the “Save” button.

Creating a Primary Key with multiple fields

When we create “Indexes”, we use field values that are as unique as possible, such as the “Primary Key”.

When we determine our “Primary Key” in a table, it can sometimes be that no single column is sufficient to function as a “Primary Key”.

Then we need to find a combination of two or more columns which together serve as a “Primary Key”.

To do this, we open the table in “Design View”.
Then we press the “Ctrl” button on our keyboards, while we select the different columns.

Once we have selected the columns we release the “Ctrl” button and click the “Primary Key” button in the toolbar.

This will be the combination of data from all selected fields set as “Primary Key”.

Removing Indexes

Should we have indexes in a table that we want to remove, we open the table in “Design View”, then we click on the “Indexes” to open the indexes dialog box.

Select the indexed row that we want to remove and click the “Delete” button on your keyboard.

Close the “Indexes” dialog box and click the “Save” button to store the structure of our table.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats