Sorting without articles

[rex.everything]

I've got a list of English and non-English periodicals that I need to sort
alphabetically by the first real word in their titles. For the English
titles, I simply leave off "The" from their records, as in "New Yorker,"
rather than "The New Yorker." This is a convention that was adopted from an
earlier database, so amending the current one with a Title Designator field,
at this point, would be time consuming and tedious. I have over 700 records.


However, I have several non-English titles that have articles in their names
that must remain in the record, such as Al Jumuah, because English speaking
users must be able to recognize the entire title to know they have the right
record, and yet, must be alphabetized by their first real words, in this
case, "Jumuah." So I want the record to read "Al Jumuah," but want the
database to sort by "Jumuah."

How do I go about designating a word within the record to be the one by
which Access sorts? Keep in mind, that the non-English articles vary in
length, such as "Al," "Les," etc.

 

[John W. Vinson]

I've got a list of English and non-English periodicals that I need to sort
alphabetically by the first real word in their titles. For the English
titles, I simply leave off "The" from their records, as in "New Yorker,"
rather than "The New Yorker." This is a convention that was adopted from an
earlier database, so amending the current one with a Title Designator field,
at this point, would be time consuming and tedious. I have over 700 records.


However, I have several non-English titles that have articles in their names
that must remain in the record, such as Al Jumuah, because English speaking
users must be able to recognize the entire title to know they have the right
record, and yet, must be alphabetized by their first real words, in this
case, "Jumuah." So I want the record to read "Al Jumuah," but want the
database to sort by "Jumuah."

How do I go about designating a word within the record to be the one by
which Access sorts? Keep in mind, that the non-English articles vary in
length, such as "Al," "Les," etc.

You can extract the first word of a string using an expression like

Left([title] & " ", InStr([title] & " ", " ") - 1)

and compare it against a stoplist table of articles. Beware though - The is an
article in English, but it's a beverage in French!

 

[KARL DEWEY]

What about adding a flag field to indicate if the first word is an article
and then in your query use a calculated field like this --
IIF([Flag] =-1, Mid([Title], InStr([title], " ") +1), [Title])

--
Build a little, test a little.

I've got a list of English and non-English periodicals that I need to sort
alphabetically by the first real word in their titles. For the English
titles, I simply leave off "The" from their records, as in "New Yorker,"
rather than "The New Yorker." This is a convention that was adopted from an
earlier database, so amending the current one with a Title Designator field,
at this point, would be time consuming and tedious. I have over 700 records.


However, I have several non-English titles that have articles in their names
that must remain in the record, such as Al Jumuah, because English speaking
users must be able to recognize the entire title to know they have the right
record, and yet, must be alphabetized by their first real words, in this
case, "Jumuah." So I want the record to read "Al Jumuah," but want the
database to sort by "Jumuah."

How do I go about designating a word within the record to be the one by
which Access sorts? Keep in mind, that the non-English articles vary in
length, such as "Al," "Les," etc.

You can extract the first word of a string using an expression like

Left([title] & " ", InStr([title] & " ", " ") - 1)

and compare it against a stoplist table of articles. Beware though - The is an
article in English, but it's a beverage in French!

 

[De Jager]

I've got a list of English and non-English periodicals that I need to sort
alphabetically by the first real word in their titles. For the English
titles, I simply leave off "The" from their records, as in "New Yorker,"
rather than "The New Yorker." This is a convention that was adopted from
an
earlier database, so amending the current one with a Title Designator
field,
at this point, would be time consuming and tedious. I have over 700
records.


However, I have several non-English titles that have articles in their
names
that must remain in the record, such as Al Jumuah, because English
speaking
users must be able to recognize the entire title to know they have the
right
record, and yet, must be alphabetized by their first real words, in this
case, "Jumuah." So I want the record to read "Al Jumuah," but want the
database to sort by "Jumuah."

How do I go about designating a word within the record to be the one by
which Access sorts? Keep in mind, that the non-English articles vary in
length, such as "Al," "Les," etc.