Sort alphabetically and ignore common words like 'A', 'An' & 'The'

Hi Experts,

I'm using Access 2003 and am sorting a list of company names alphabetically. I'd like Access to ignore common words such as 'A', 'An' & 'The'.

Does anyone know how to do this in an Access query?

Regards,
troycox
troycoxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
Firstly you may find it easier to have e field the user enters as the Sort Field so they enter "The Company Name" in the CompanyName field and "Company Name (The)" in the CompanyNameSort field.

Otherwise I think you will need to

1. Create a table to contain all the words you want to ignore
2. Create a function that chacks the table and eliminates all the words in it.
3. Create a query that calls the function passing the Company Name field and sort its results.

If you are using a large number of records the function will be slower

Cheers, Andrew
0
jadedataMS Access Systems CreatorCommented:
Hey troycox!

  You will need a function to accomplish this.

  Public Function SortThis(sSortOn) as String
   
    dim IgnoreThese as Variant, ix as integer
    dim tmpSortkey as String

    IgnoreThese = Array("A ","An ", "The ", " a ", " an ", " and ")
    for ix = 0 to ubound(IgnoreThese)
      tmpSortKey = replace(sSortOn,IgnoreThes(ix),"")
    next ix
  End Function

  The function replaces the elements of the array when found with a zero len string.  The remaining portion of the string can be sorted without the undesired parts.

regards
Jack
0
TextReportCommented:
Oh I forgot to mention that if you get the funcvtion to work then you could use it to populate the CompanynameSort field automatically then your query will be able to use an index and be more efficient.

Cheers, Andrew
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jadedataMS Access Systems CreatorCommented:
you can add elements to the array as you think of them...  or like Andrew says, tablize them and draw them from there.  (a dictionary table)
0
TextReportCommented:
Jack the problem with your code is that you would be reenaming jadedata to jadedat

Also are you sure that spaces are the only word seperation character you need to handle.

Cheers, Andrew
0
jadedataMS Access Systems CreatorCommented:
hmmmm...
0
TextReportCommented:
Jack, this was why i didn't even try to do the code it raises more questions than answers.

BTW this is what you can do with searches under Internet Information Services, it could be that 2003 has something built in. The words like The An are refered to a NOISE words (I think)

Cheers, Andrew
0
troycoxAuthor Commented:
Hi guys,

I did a search in Access 2003 help and couldn't find any built-in feature that could ignore 'noise words'. Looks like I'll have to find a custom solution.

I have pasted jadedata's function into a common module and then used it in a query as follows:

SELECT tblReferrers.[Referrer ID], tblReferrers.[Referrer Description], SortThis([Referrer Description]) AS SortName
FROM tblReferrers
ORDER BY SortThis([Referrer Description]);

Strangely, it seems to do nothing. In other words, the result set is not sorted in any way and I get no errors. In fact, the SortThis column is blank.

Where am I going wrong?

Also, I only need to ignore words at the beginning of the name so maybe the function can updated to only process the first word??

For example:

I have in my list:

Homeside
Realty One
The Arthichoke Group
The Investment Network

It should sort as:

The Artichoke Group
Homeside
The Investment Network
Realty One

Regards,
troycox
0
TextReportCommented:
Jack to expand on your function ( and to debug the error as you keep resetting the tmpSortKey so it only returns the last one from the array)

Cheers, Andrew

Public Function SortThis(sSortOn) As String
   
Dim IgnoreThese As Variant, ix As Integer
Dim tmpSortkey As String

    IgnoreThese = Array("A", "An", "The", "a", "an", "and")
    tmpSortkey = sSortOn
    For ix = 0 To UBound(IgnoreThese)
      tmpSortkey = Replace(tmpSortkey, " " & IgnoreThese(ix) & " ", " ")
      tmpSortkey = StripMultipleSpaces(tmpSortkey)
     
      If Left(tmpSortkey, Len(IgnoreThese(ix)) + 1) = IgnoreThese(ix) & " " Then
         tmpSortkey = Mid(tmpSortkey, Len(IgnoreThese(ix)) + 1)
         tmpSortkey = StripMultipleSpaces(tmpSortkey)
      End If
     
      If Right(tmpSortkey, Len(IgnoreThese(ix)) + 1) = " " & IgnoreThese(ix) Then
         tmpSortkey = Left(tmpSortkey, Len(tmpSortkey) - Len(IgnoreThese(ix)) + 1)
         tmpSortkey = StripMultipleSpaces(tmpSortkey)
      End If
     
    Next ix
   
    SortThis = (tmpSortkey)
   
End Function

Function StripMultipleSpaces(pstrString As String) As String
Dim cnt As Long
Dim strString As String

   strString = Trim(pstrString)
   Do Until InStr(strString, "  ") = 0
      strString = Left(strString, InStr(strString, "  ") - 1) & " " & Trim(Mid(strString, InStr(strString, "  ")))
   Loop
   
   StripMultipleSpaces = strString
   
End Function

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
troycoxAuthor Commented:
Hey TextReport,

That worked perfectly!!! And I agree with you, MS should add this as a built-in feature of Access.

I love this service!

Regards,
troycox
0
TextReportCommented:
Jack in recognision of your part in starting this function I have created a points for question.

http://www.experts-exchange.com/Databases/MS_Access/Q_20812138.html

Cheers, Andrew
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.