Use Index in Access 2016

Index is a special data structure designed to improve data access speed. Simply put, an index is a pointer to each value that appears in the indexed table / column, which has the same meaning as the entries in the Index of a book.

1. In some cases, such as for a primary key, Access automatically creates an index for you.
2. The index stores the location of the records based on the selected fields.
3. After being provided with an index location, Access retrieves data by moving directly to that location, performance and time are significantly faster than scanning through all records to find data.
4. Indexes can speed searches in queries, but performance may also slow down when adding or updating records.

For example

Continuing to manipulate on the Access database that we have practiced in the previous lessons, TipsMake.com will guide you how to identify automatically created indexes, how to create and delete an index as you wish.

First, you need to remember, any field set as primary key in Access is the default of an index. Access creates additional sub-indexes depending on the name of your fields.

To check, go to the File menu , select Options.

Options window opens, select Object Designers.

In AutoIndex on Import / Create, you will see ID; key; code; num in the corresponding text box. So by default, the index will be automatically added to fields with a name that starts or ends with "ID", "key", "code", "num".

If you want to add an index to a field, go to the Field tab , black out the required field and check the Indexed box in the Field Validation field. Example with table tblNhanvien in database:

You also have alternative options to create or delete bookmarks. Go back to Design View view , select any field and then follow the Indexed section in the detail properties area at the bottom of the screen.

You can change it by clicking on the drop-down menu and selecting the mode that suits your needs:

1. No: No index set.
2. Yes (Duplicates OK): allows you to enter the same records on this field.
3. Yes (No Duplicates): do not allow to enter the same records on this field (usually lock fields).

Now try to create an index for the Ho field in this table. In Indexed, select Yes (Duplicates OK) for this field and Save table, Access noted that you created the index

Another area you can see and adjust the index for the table is in the Design, Hide / Show group tab .

Click Indexes, you will see a list of all indexed fields in this table.

Previous article: Calculation expression in Access 2016

Next lesson: Wildcard representation operator in Access