Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54

    Unanswered: Find most similar records

    I'm trying to open a form and find the records that are MOST LIKE a particular value. Lets say I have the value "Will." I would like the form to respond with records starting with the most similar first. For instance: "Wills" "William" "Wilbur" "Wiley" and "Wall" (in that order).

    I know how to use the "like" operator, but this would result would exclude Wilbur, etc. Using "Like 'Wil'" or "Wi" I get the results sorted alphabetically instead of by best match.

    In other words, i want to somehow value the similarity, then sort them by that similarity value.

    Any ideas? TIA.

    Matt

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This is just a thought. Have a table which has the fields, 1 field labeled: SearchWord and then have 3 or 4 other fields labeled: LikeValue1, LikeValue2, LikeValue3 and 1 last field labeled: SimilarityStrength (integer).

    Utilitilize the instr function...

    For example: Will is stored in the SearchWord field. W in LikeValue1, i in LikeValue2, and L in LikeValue3. (or Wi in LikeValue1, Wil in LikeValue2, Will in LikeValue3) (or Wills, William, Wilbur, Wiley, Wall in the LikeValue1,2,3,4,etc...

    In your query or sql string you then have some kind of routine which returns the LikeValue1, LikeValue2, LikeValue3 and SimilarityStrength for whatever SearchWord you looking for. For example the SearchWord = Will
    IIf instr(MyWord,LikeValue1) and instr(MyWord,LikeValue2), and instr(MyWord,LikeValue3) return SearchStrength and mark it as a match.

    OR

    Set it up so you only have 3 fields: SearchWord, LikeValue and SimilarityStrength.
    Then have records like those below
    SearchWord.........LikeValue........SimilarityStre ngth
    Will.....................Wi..................1
    Will.....................Wil.................2
    Will.....................Will................3
    or
    Will....................Wills.................1
    Will....................William..............2
    Will....................Wilbur..............3
    Will.....................Wiley..............4
    Will.....................Wall...............5

    You would need to do some kind of loop then against the above table and utilize either the Like or instr functions against the LikeValue field retrieving the SimilarityStrength.

    These are just some ideas which may or may not work for you since this isn't your normal Like statement and you want the strength of the similarity. Hopefully these will get you thinking about different ways you want to do it.

    Either way, I'm thinking you'd want some sort of "Lookup" type table where you create the SearchWord and then fields with partial letters of that SearchWord designating the strength of each partial letters.

    If I thought more on it, I might be able to come up with something working but I would need more examples. There's a lot of latitude here so I don't know how you could do it other than establishing a table of values to search upon returning the strength of those values.

    Hope that helps give you some ideas.

    Lastly, there might be a setting somewhere in MSAccess which gives you similar words. I'm not sure but I thought I saw some place where it let you set something for similiar words. I think it's called Soundex and it's a function you can utilize for similar sounding words but you may want to read up on it.
    Last edited by pkstormy; 05-24-07 at 23:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    http://www.dbforums.com/showthread.p...t+by+relevance

    Rudy even wrote an article about this which can be found here:
    http://r937.com/keyword_relevance.html
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54
    That definitely gets my brain going, Paul. I like the concept and hadn't thought of the instr command (thought i'd looked through them all, too). That may be the catalyst that I needed. Thank you!

    And thanks, Georgev for the article - I will take a look at it. I had searched for other references & forum threads, but probably was not using the right combination of search terms as I wasn't getting to anything meaningful. Thanks!

    Matt

Posting Permissions

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