Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: Omitting articles "the", "a" and "an" when sorting data - Newbie!

    I'm working on a bibliography and a friend (who can't speak English) is building an Access db for me.

    In the index at the end of the book, I need titles (of poems, stories, etc) to be alphabetically arranged, but the articles "the", "a" and "an" should be ommited when sorting those titles. For instance:
    "A Stupid Mistake"
    "The Days"
    "Hell, Yes"

    should be actually listed/indexed as:

    "The Days"
    "Hell, Yes"
    "A Stupid Mistake"

    Is it easy to do? Is there a built-in function of sorts to do this in Access?

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id suggest you create an additional column, called say IndexEntry (string Column)
    then create a series of queries which do what you want
    the first one copies the title to the new column
    update mytable set IndexEntry = Title 'that sets the IndexEntry column to be the same as the title
    then run as many queries as you need to remove what ever words you need
    eg:-
    update mytable set IndexEntry = mid(IndexEntry, 3) where left(indexEntry,2) = "A "
    update mytable set IndexEntry = mid(IndexEntry, 3) where left(indexEntry,2) = "I "
    update mytable set IndexEntry = mid(IndexEntry, 4) where left(indexEntry,3) = "AN "
    update mytable set IndexEntry = mid(IndexEntry, 5) where left(indexEntry,4) = "THE "

    arguably that coudl be restated as
    update mytable set IndexEntry = mid(IndexEntry, 3) where left(indexEntry,2) in ("A ", "I")
    update mytable set IndexEntry = mid(IndexEntry, 4) where left(indexEntry,3) in ("AN ", "IS ", "OF ")
    update mytable set IndexEntry = mid(IndexEntry, 5) where left(indexEntry,4) in ("THE ", "AND ", "BUT ")

    those queries could be run manually or put into a macro and run one after the other.

    then when you retrieve the data set the ORDER BY clause to be IndexEntry
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Posts
    3
    Thanks, that's very useful. I'll tell my friend to do as suggested.

    But I'm surprised this is not in Access as a built-in function of sorts. Omitting those articles is a must in any index, checklist, etc.

    In many cases, standardization of titles is required in bibliographies and checklists, and Access has the "proper case" function to do that. A function to omit those articles when retrieving and sorting data would be very useful, I think.

  4. #4
    Join Date
    Dec 2011
    Posts
    3
    Would it be possible to automatically invert those articles?

    "Days, The"
    "Hell, Yes"
    "Stupid Mistake, A"

    Or would it require some programming as well?

    thanks!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes it could be doen at the same time the leading word is dropped, but it would have to be done for each word
    eg

    update mytable set IndexEntry = mid(IndexEntry, 3) & ", " & left(IndexEntry,1) where left(indexEntry,2) in ("A ", "I")
    update mytable set IndexEntry = mid(IndexEntry, 4) & ", " & left(IndexEntry,2) where left(indexEntry,3) in ("AN ", "IS ", "OF ")
    update mytable set IndexEntry = mid(IndexEntry, 5) & ", " & left(IndexEntry,3) where left(indexEntry,4) in ("THE ", "AND ", "BUT ")

    ..seriously if your db designer cannot do this sort of thing then you perhaps need to rethink if they are up to the job.

    I did look at trying to see if you could do a generalised query matching specified words against the leading word in the title. I think its possible, but I have neither the time or inclination to try and make it work
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •