[AccessD] Indexes on text fields

Nope, I cannot give you and sources and will not spend my time looking.  Go
look in any book on Access queries, sorting and indexing and I'm sure you'll
find your own.

There is no index on any field that is an append of other fields.  Access
doesn't make any attempt to use indexes on the fields used since I can
append anything I want in the stuff in the append that is not field related,
which throws such indexing out the window.

Think about it for a minute.

[Fielda] & Random() & [Fieldb] & ascii(right$("John Colby",1))

How in the world is any index on FieldA and FieldB going to help you sort
that expression into logical order?  How in the world is Access going to
determine whether to even try?  The resulting string expression can be
ANYTHING!!!

Access does NOT use the indexes on the fields used to build up an appended
string.  If you apply a sort order to such a string, Access will do the sort
on the resulting string, on-the-fly, and it will be PAINFULLY slow!!!

The best you can do if you know that the results will be useful is to use
the actual fields in the query, apply the sort order on those fields and you
end up with the required results.

LName - Sort Ascending
FName - Sort Ascending
MyAliasedField: [FName] & ", " & [FName]

will give you the same results as

MyAliasedField: [FName] & ", " & [FName] - Sort Ascending

But WILL use any existing indexes on LName and FName to get the result
sorted as you desire.

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Thursday, August 14, 2003 9:49 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields


John,

  Can you site the source please?

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Thursday, August 14, 2003 9:40 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields


Yep, I'm sure.

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Thursday, August 14, 2003 8:33 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields


John,

  <<Nope.>>

  Are you sure about that?  JET would certainly know about the base indexes
on those fields from the costing plan.  Then again, JET may build it's own
index for the query output.   Hard to say if you don't know the internals.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Wednesday, August 13, 2003 5:59 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields


Nope.  Concatenated fields are just a text string to the query engine.
However if you dragged the last and first name out into the query (even if
hidden), then sorted on those fields, it would sort in that order (of
course) and indexes on those fields would speed the sort.

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark
Whittinghill
Sent: Wednesday, August 13, 2003 2:33 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Indexes on text fields


Let's say I have a Person table with FirstName and LastName fields.  Both
are non-unique indexed to help searches.  Now say I build a query with a
concatenated field FullName: LastName & ", " & FirstName.   Will the indexes
on these fields help speed searches on the FullName field?

Mark Whittinghill
Symphony Information Services
612-333-1311
mwhittinghill at symphonyinfo.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com