Solved

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

Posted on 2003-11-30
11
657 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:troycox
  • 6
  • 3
  • 2
11 Comments
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 32

Expert Comment

by:jadedata
Comment Utility
hmmmm...
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 

Author Comment

by:troycox
Comment Utility
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
 
LVL 28

Accepted Solution

by:
TextReport earned 500 total points
Comment Utility
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
 

Author Comment

by:troycox
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now