Results 1 to 5 of 5
  1. #1
    atmcd is offline Novice
    Windows Vista Access 97
    Join Date
    Sep 2010
    Posts
    3

    Question Multiple keywords search of multiple fields

    I have a keyword table with 119 records or keywords (keywords include a full name or company name). I have another table with 12,000 records and need to find all records based on the 119 keywords found in 22 different fields. Is there a better way with a SQL statement instead of writing hundreds of LIKE queries?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    this might be slow, but you can use my SumField() function to help you out if you're doing it in an actual query: http://www.ajenterprisesonline.com/a...SumFields.html

    Or another possibly is to use this function to help you out: http://www.access-programmers.co.uk/...d.php?t=197449

    You can easily change that code to be called in the query. I'm not sure how slow it would be, but you could set it to search the current record for all 119 entries.

    I'm guessing this will be slow no matter what because the tables are unrelated. You might be stuck with a lookup procedure for each record at the very least, which is always slow. The best option here would be for you to rearrange this data so it is easily searchable.

  3. #3
    atmcd is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    3
    http://www.access-programmers.co.uk/...d.php?t=197449 This set of code looks promising but of course I'm that "novice" SQL/VB user not comfortable with complex code. Would "INT", "OUT", "DO" represent the queries or tables to be searched, not sure where it would seek the keywords table. Also, not concerned about speed, only isolating those records with the keywords.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by atmcd View Post
    http://www.access-programmers.co.uk/...d.php?t=197449 This set of code looks promising but of course I'm that "novice" SQL/VB user not comfortable with complex code. Would "INT", "OUT", "DO" represent the queries or tables to be searched, not sure where it would seek the keywords table. Also, not concerned about speed, only isolating those records with the keywords.
    Well I'm glad to hear that you're not concerned about speed, because the methods that I suggested would be slow. The AWF link I don't think is the best because it has to be modified in order for it to suit you. If I were you, if you don't come up with any other solution, I would export both tables to Excel and make use of it's VLOOKUP() function or something similar to that. It would much MUCH faster doing it this way that to try and write a UDF that covers this need. It would even take me a long time to figure out how to write it and I've been coding the language for 10+ years.

  5. #5
    atmcd is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    3

    Excel Advanced Filter did it

    Thanks for mentioning Excel, I was able to use the Advanced Filter and use the 119 keywords as the criteria range for the 22 fields using a macro.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search for multiple records
    By Blake in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:17 AM
  2. Query that would search multiple critera and delete
    By justinwright in forum Queries
    Replies: 2
    Last Post: 08-25-2010, 10:05 AM
  3. How to Search for keywords in a Query
    By Brian62 in forum Queries
    Replies: 2
    Last Post: 05-24-2010, 11:14 AM
  4. How can I search multiple tables?
    By botts121 in forum Access
    Replies: 4
    Last Post: 02-02-2010, 06:39 AM
  5. Replies: 1
    Last Post: 12-10-2009, 08:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums