Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Look up using key words (Win XP Access 2003)

    I need to look up information in the products table based on keywords in the keywords table.

    I have a table of keywords and a table of products, linked on the product ID in a query. The query takes the data from a form, which has space for three key words. It works except for the limitation of the number of words. Is there a better was to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look up using key words (Win XP Access 2003)

    Can you privide some information about how it works now, or attach a stripped down and zipped copy of the database?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Look up using key words (Win XP Access 2003)

    This is usually as good an approach as any. It depends alot on how many more search words you want to enter, and whether or not you are doing exact matches on the keywords.

    An alternative approach would be to enter the keywords you want to search on into a temp table, let's call it [tempSearch], with 1 word per record. Then, create a query joining this to your Product-keyword table. The advantage of this approach is that it isn't contrained by the # of words you are search for. One such approach might look something like this:

    SELECT ProductID, Count(KeyWord) as CountOfHits FROM tblProductKeyWord INNER JOIN tempSearch ON tblProductKeyWord.KeyWord = tempSearch.KeyWord GROUP BY ProductID

    This gives you a list of all Products that had 1 or more "hits" in the keyword search, and also returns the # of hits. You could return each "Hit" if you wanted, rather than just 1 record per Product. This query also assumes you are doing an exact match; doing partial matches would require a different join and a WHERE clause using the LIKE operator.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up using key words (Win XP Access 2003)

    Here is the stripped down version, as requested. Do you guys ever sleep?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look up using key words (Win XP Access 2003)

    > Do you guys ever sleep?

    When you don't look <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    You could use one text box in which the user can enter any amount of keywords, separated by spaces, e.g.

    rats dogs snakes bugs

    Remove the existing criteria from the query, add a new column with the expression
    <code>
    InStr(" " & [Forms]![LUParameters]![FirstWord] & " "," " & [KeyWords] & " ")
    </code>
    and set the criteria for this column to >0

    See attached version.

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up using key words (Win XP Access 2003)

    I

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look up using key words (Win XP Access 2003)

    The InStr function either returns the position of the first match, or 0 if there is no match. For example:

    InStr("Wooly Bullly", "ly") returns 4, since the first occurrence of "ly" in "Wooly Bully" starts at position 4.
    InStr("Wooly Bully", "dy") returns 0, since "dy" doesn't occur in "Wooly Bully".

    So the criteria >0 for the InStr expression in fact means that the keyword does occur in the FirstWord text box. The spaces before and after both the text box value and the keyword are to ensure that we're looking for whole words only, not parts of words.

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up using key words (Win XP Access 2003)

    I added an additional combo box to restrict the search of key words to a certain category. It works fine, but if I want to search all categories, what do I put in that combo box? By the way,. The info in <post:=536,547>post 536,547</post:> was very helpful. You guys are incredible! Thank you.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look up using key words (Win XP Access 2003)

    One possibility is to search all categories if the combo box is empty. You'd handle the "null" case separately in the code.

    For an other option, see Adding an (All) item to an Unbound Combo Box. You'd handle the "(All)" item separately in the code.

  10. #10
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up using key words (Win XP Access 2003)

    I used the

  11. #11
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up using key words (Win XP Access 2003)

    I guess this was inevitable. How do I handle partial matches, for example I would like for "ra" to return "rat" and "rats"? Also, is it possible to require that various key words be matched only if they appear together. As an example, I would like to request that "dog" AND "cat" both be present for the match to suceed.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look up using key words (Win XP Access 2003)

    One possibility would be to use a syntax such as used by some search engines, with AND and OR and NOT etc. It would require writing a parser for the keywords string, not a trivial task.

    Another possibility is going back to separate text boxes for the keywords, plus option buttons to let the user select AND or OR to combine the criteria. You'd have to build the selection criteria in code.

    Perhaps the easiest solution is to do away with all this, and to teach the users how to use Form Filter or Advanced Filter.

Posting Permissions

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