An index is something a database, not just Access, uses to access
information quicker in a table. The index is maintained in a sorted order.
Without the index, if you are doing a search of the records the database
must start at the first record and go through each record until it finds
what you're looking for. If what you want is near the end of the table, this
may take awhile.
With an index, since the index is sorted, the database uses a different
logic. Instead of starting at the beginning and going through the records
one at a time, it will start in the middle and ask "is what I'm looking for
above or below this record?" It will then move half way in the direction
indicated and try again. This works much quicker unless the record just
happens to have been one of the first few records in the table, but on
average it is MUCH faster.
Indexes can slow things down also. For example, if you are using an append
query to add a bunch of records at once, the database must update the index
as each record is added. It is sometimes quicker to delete the index, add
the records, then recreate the index.
The key is to only index things you need to. As mentioned above, they do
require a little overhead. For the most part, index any field that you will
be doing a lot of searching on. Also, Access will create and index for any
field that you create a join between tables on in the Relationships window,
although this index won't show in the table design view.
The Yes, With Duplicates and Yes, No Duplicates will cause Access to enforce
unique values in the field if you choose the No Duplicates option. When Yes,
No Duplicates is chosen for the index on a field and you try to enter a
value in the field that already exists, you'll get an error. Access will set
a Primary Key field to Yes, No Duplicates automatically, because the Primary
Key should be unique for each record.
It is also possible to set an index across multiple fields, with the same
Duplicates (yes/no) option as an index for a single field. If set to No
Duplicates, this will force the combined data in all the fields in the index
to be unique.
Example:
Field1 Field2
AA BB
AA CC
This would be allowed. While there is a duplication in Field1, the
combination of Field1 and Field2 is unique.
