Complex Indexing

[TC]

Can Access create a complex index.

Access can index on one or more fields, for example:
surname
surname, forname
surname, forname, midname
etc.

but it can not index an *expression* (eg. surname & forname & midname),
unless you store the result of the expression (redundantly) as a seperate
field, and then index on that seperate field.

So:

Example 1
A table with three fields first, middle and last names.
Can Access create an index by concatinating:
trim(last) & " " & trim(first) & " " & middle

Not unless you store that as a seperate field. But why not just have a
3-field index (last, first, middle)? Then it is efficient (ie. uses the
index) with any of the following finds:

last=SMITH
last=SMITH and first=JOHN
last=SMITH and first=JOHN and middle=P

Of course, it is not efficient (does not use the index) for the following
finds - but these would not use your "trim & trim" index, either:
first=JOHN
middle=P

Example 2
A table has among other fields company and contact name
fields. Can Access create an index using the the immediate
if:
iif(len(company)>0, company, [contact name])

A workaround is to create another field in the table with
redundant data. Butt ugly.

No - as above. But so what? Have one field for company name, & another for
contact name. If you need to find by company name, create an index on
company name. If you *also* need to find by contact name, create another
index, this one on contact name. Voila! Finding by either field, is
efficient.


HTH,
TC

 

[Terry@Large]

Can Access create a complex index.

Example 1
A table with three fields first, middle and last names.
Can Access create an index by concatinating:
trim(last) & " " & trim(first) & " " & middle


Example 2
A table has among other fields company and contact name
fields. Can Access create an index using the the immediate
if:
iif(len(company)>0, company, [contact name])

A workaround is to create another field in the table with
redundant data. Butt ugly.

Thanks for the help,
Terry

 

[Paul Overway]

In your 1st example, if last, first, and middle are already indexed...there
is little point in creating an additional field to index. It won't provide
any additional benefit in respect to searching. Likewise with your 2nd
example.

 

[Tim Ferguson]

Example 1
A table with three fields first, middle and last names.
Can Access create an index by concatinating:
trim(last) & " " & trim(first) & " " & middle

Why would anyone want to..? You'd only end up with names in the wrong
order: for example

Eric Van
Lee Van Cleef
Damien Vanderblitz

would sort into

Van Cleef Lee
Van Eric
Vanderblitz Damien

which is not what you want. The normal way is simply to sort on the three
fields in the order you suggest

CREATE INDEX FullName MyTable (LastName, FirstName, MiddleName);

Example 2
A table has among other fields company and contact name
fields. Can Access create an index using the the immediate
if:
iif(len(company)>0, company, [contact name])

You cannot create an index like this, and you probably would not want to
given the impact it would probably make on any kind of update to the table;
but you certainly can sort a recordset:

ORDER BY IIF(Company IS NULL, ContactName, Company)

bearing in mind that empty fields are NULL rather than "" unless you have
enabled AllowZeroLengthFields and taken pains to enter the "" values by
code.

HTH


Tim F