Compound Indexes

[Ed Robichaud]

In general, you would want to index all controls/fields that you would later
use to sort/filter in queries and reports. A "name" index (Lname,Fname,MI)
is a common compound index example. Another might be invoiceDate,
productID, etc.
However, you tables should almost always have a single unique ID field as a
primary key and that would be used in building relationships to other
tables.

-Ed

 

[John Vinson]

Coming into Access 2003 from other database work. I'm wondering what the
advantage of a compound index might be. Is it's effectiveness (if any)
depend on the database being used (Jet or some SQL backend?)

=Alan R.

Compound indexes are frequently used to ensure uniqueness across a set
of fields: for instance, if you want to require that the combination
of FirstName, MiddleName, LastName, Suffix and Address be unique
(allowing any one of these fields to be duplicated), you can create a
multifield unique index. Nonunique multifield indexes have some value
- for instance an index on LastName, FirstName will make searching or
sorting on those two fields (sorting lastname first) more efficient
than having no index, and maybe a trifle faster than if you had
separate indexes on the two fields.

 

[Alan Reinhart]

Coming into Access 2003 from other database work. I'm wondering what the
advantage of a compound index might be. Is it's effectiveness (if any)
depend on the database being used (Jet or some SQL backend?)

=Alan R.

 

[Alan Reinhart]

John -

Thanks for the info - it makes good sense!

=Alan R.

 

[Alan Reinhart]

Ed -

Thanks for the confirmation.. that was how I was beginning to imagine it. I
would think the compound index should be of some assist to the
query-builder.

=Alan R.

 

[Rolls]

A compound index will allow you to do the same thing you would accomplish by
concatenating several fields and then indexing that one new field, without
the overhead of creating the new concatenated field as additions and changes
to the table occur. The reason for either method would be to obtain a
unique value and prohibit duplication of the full combination all fields.