Lesson 10ª







Indexes based in several fields

We finished the previous lesson with the "Indexed" field. This field helps Access find a piece of information quicker. When we mark a field as "Indexed", Access builds an index for it. An index works in the same manner as an index in books. It orders our Records so that when Access needs to find a specific one, it uses the index to find it quicker.

Access makes this in the background. In other words, we won't see it happening but we will notice that searches are done faster.

The properties for an "Indexed" field has three options:
No: Doesn't create an index for the field. If it has one, it deletes it.
Yes (Duplicates): Creates an index for this field
Yes (No Duplicates): Creates an index without duplicates

This last option means that there can't be any duplicate data, for example, we won't be able to create an Index in a First Name field if 2 or more records are called the same.

We can create as many indexes as we like. However, keep in mind that they also increase the size of our Data Base. Therefore, our advise is to create indexes for those fields we know we will be using constantly when searching for Records.

In the Table Tools, under the Show/Hide sub-menu, we have the option "Indexes"

After clicking this icon, we will be able to see all the Indexes we have created for our table.

We find 2 indexes for the "Orders" Table, one for the "Order Code" and another for "Vendor". In the list of our Indexes, we see another called "Primary Key", but it is the same as "Order Code". Remember that we defined the "Order Code" to be the primary key of the "Orders" table, so in every table with a defined Primary Key, there will be an Index called "Primary Key". It makes reference to the "Key" field. It is independent from the other Indexes we have created.

On the "Index Properties", we find 3 characteristics:

"Primary": it is the characteristic of the Key of the Table; there can only be one such index.
"Unique": it indicates if there can be duplicates or not.
"Ignore Nulls": It excludes the null values from the Index. For example, empty Records won't be organized.