Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Access 2013 query: Ignore articles (a, an, the)

    I thought surely someone has asked this question, but a search of the forum turned up nothing. Maybe I'm not asking the right question.

    I have a table of movie titles that I own, and I've built a query to sort the titles. Of course, many movie titles start with the articles "A," "An" or "The." How do I force the query to ignore such words?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Why not have another field for title, say TitleAlternate where you don't store these words, the query using the new field.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    That's certainly one possibility. Thank you.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    You could create a public function that accepted the original Title and returned a string suitable for sort, and use it in queries, reports, and such.

    But I think having an Alternate Title as patt suggested would probably be the best solution, as you could then handle any out-of-the-ordinary situations that crop up. As for populating the Alternate Title field, you could still use that new function such that during data entry you would call this function in the AfterUpdate event of the Title textbox and populate the Alternate Title textbox; and only changing the results if needed.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    I like that idea, Mark. Thanks!

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    No problem. Good luck with it. If you need help with the function, just give a holler.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    565
    Thanks
    51
    Thanked 70 Times in 68 Posts
    There are multiple ways of handling this situation and no "perfect" way. That's the first thing to know.

    You can:

    1). Create an alternate Search Title column with the articles removed. This solves the issue at the cost of more table space used. One great advantage of this solution is that indexes can be created over the new column and indexes are one of the primary ways databases use to get good performance (traditionally);

    2). Simply modify the column you have now. The usual answer to "what about the article" is to move it to the end of the title, as in "The Happening" becomes "Happening, The". Since the articles are still there, the titles are still complete. Note that full-text searches will still find them, which may or may not be a good thing. The column is indexable which is very nice;

    3). Ignore the problem. Seriously, this can be viable sometimes! If you always perform full-text keyword searches, maybe reporting groups of movies sorted in a list isn't important to you. Also, I note that most databases of movies aren't actually going to be that big and will fit entirely into memory. In this case indexing isn't that big a deal either;

    4). You can create a fancy view with logic in it to detect those leading articles. The view logic then restates the title and (presumably) sorts the output based upon the restated titles. How you restate the titles is up to you, but realistically the only sensible options are to simply remove the articles or put them at the end of the title. Now when you query the database for titles, query the view instead of the underlying table;

    5). There are systems for databases, sometimes add-ons, that create full-text, indexes for text columns. These systems are designed for scalable, high capacity keyword searches. Such systems often have full support for the automatic removal of article words as needed, and they can often do more as well. I'm not sure about Access, but in SQL Server the option is called "Create Fulltext Index".

    The important thing to understand about these text indexing systems is that they do not simply create an index with the entire contents of a column. They parse the column into individual words and can then do additional things with each of those words. This is at variance with database theory which assumes that each column is atomic, and therefore does not need to be broken down into parts.

  8. The Following User Says Thank You to BHarder For This Useful Post:

    Caesar3 (2015-05-07)

  9. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    That's a very clear assessment! This is not a big project, of course, so I'm going with 1 or 2. Thanks for the reply!

Posting Permissions

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