Table Design - Sorting Article Titles

[Michael]

Hi Folks - I am building a database of social work articles. The articles
have titles such as:

A Formative Evaluation of the Drug Court
An Examination of Culturally Competent Service Delivery System of Care That
Reflect the Needs of Ethnic Populations
The Integration of Religion/Spirituality and Counseling: A Study of Client
Views and Experiences

Is there a way to build the table so that when it sorts, it excludes the
leading A, An or The? Or, is there a way to sort the data without including
the A, An or The? I'm looking for best practices for this type of situation.
Thanks.

Michael

 

[Allen Browne]

Michael, the most efficient solution might be to add a SortAs field to your
table. In the AfterUpdate event procedure of the text box where the articles
are entered, call a procedure that chops off the leading words you do not
want, and adds them as a sufffix if desired. This approach can also do
things like sorting Macdonald as McDonald.

The major advantage is that Access can use the index on the SortAs field to
instantaneously sort or match titles, instead of having to pass every record
through some kind of function at runtime and then apply the sorting on the
function results, which would be much slower if you have many thousands of
titles, and several replaces to perform.

 

[Michael]

Allen - Great suggestion ..... Thanks.

Michael