Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Sorting by Relevance

    First up I'd like to say that this is just a theoretical question that may get implemented in the future.

    So lets say I have a memo field that I want to search for keywords... If it's a single keyword then I use LIKE '%example%'...
    Now if I had two keywords that I wanted to search, I could do something very similar: LIKE '%example%' OR LIKE '%eg%'.

    Ok, so how would I do this for an unlimited number of keywords, then order the results be relevance (ie, top would be the one that contained the most keywords and bottom would only contain one of the keywords).

    Any suggestions?

    If I've not been clear enough then please ask me any questions needed to gain an answer Ta!
    George
    Home | Blog

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    shoot... Rudy's already done it. Guess I'll have to do real work instead
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Memo fields are meant to hold just that, memos! They should never, never , never be used to hold data that you may wish to manipulate! The problem comes up so often that I have a reference just to address, named MemoFieldAngst:

    Crosstab queries, Summary queries, Union queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates.

    Also, if you have specified a format in the field's Format property, this will often truncate the data to 255 characters as well.

    If Unique Value Property is set to Yes, Access has to compare the values and once again Memo Field values are truncated.
    I wish you luck, but I suspect you're not going to be successfull in your quest!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by georgev
    First up I'd like to say that this is just a theoretical question...
    Sorry, text field.
    It was just an idea that was floating around in my head that I thought I'd share
    Thanks for the quick responces "dudes and dudettes".

    I will take a better look at your response tomorrow Rudy - home time!
    Last edited by gvee; 03-14-07 at 08:26.
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    rudy, great article: well written, easy to understand and follow complete with a great little example.

    I do have something to add: Your last comment is about trailing spaces and leading spaces... would it be worth trimming the descriptions before performing any calculations? (not sure if that's possible in mySql...).

    So here's the extension to the question:
    We give the user a textbox they can write their keywords in - so they're free to add as many or as few words as they like. So in this example our user decides to search for "i like games" as per Rudy's example.

    How do we dynamically search for multiple keywords and sort by relevance?
    George
    Home | Blog

Posting Permissions

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