Results 1 to 9 of 9
  1. #1
    lhanley22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2021
    Posts
    7

    Searching a Database based on keywords when there are multiple key words per data piece.

    Hello,


    Apologies if this is too broad. I am new to access, and I am creating a database of documents that have single phrase tags. Each piece of data has multiple word tags. I want to be able to search the database with a search form based on these tags. At first I used a lookup field with another table to choose the tags from the dropdown on my main database table. I was unable to search based off these tags. Any advice on a simple/good way to go about this.
    Thank you

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    some examples of what this means will help us to help you

    database of documents that have single phrase tags. Each piece of data has multiple word tags.
    please provide example data that covers all eventualities and the outcome required for different searches on that data

  3. #3
    lhanley22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2021
    Posts
    7
    Yep I can give that. The first image is some example data. These are construction jobs with some other fields. The tags are shown to the right. Id like to create a form with keyword boxes where I can type in a tag and it will show me the jobs that had that tag, along with the other fields. I have been able to search by the fields that only have one term. "office" for example on my data. I'm just having trouble searching by the field that has multiple terms in it. An example of what I want is in the second picture:
    Thanks

    Click image for larger version. 

Name:	data example.jpg 
Views:	33 
Size:	42.7 KB 
ID:	45792Click image for larger version. 

Name:	refsearch.jpg 
Views:	31 
Size:	105.3 KB 
ID:	45793

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not a lot of help I'm afraid. You talk of tags but I see keywords in the top image - actually they are phrases. And the second image bears no relation to the first with either keywords or record returned.

    And I note you have the opportunity to add 2 more keywords. What is supposed to happen when they are completed - return records where they are all present? or any one of them?

    Don't be lazy, provide some proper examples as I requested - please provide example data that covers all eventualities and the outcome required for different searches on that data. Providing a couple of screenshots take you a few seconds - takes us a lot longer to understand and we still don't get the full picture. If you want help, you need to provide the information requested.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Consider posting your dB. I am wondering about your table designs.
    The "Keyword" field violates the rules of normalization. I would have the "Keyword" field values in their own table.

    Please note that "Date" and "Year" are reserved words (and built in functions) in Access.


    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.

    "Cat #" and "Project #" - bad names because of the space and the special character (the hash mark)"
    "Job Title" and "Main Feature" - has spaces
    Last edited by ssanfu; 07-21-2021 at 01:16 PM. Reason: spelling

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMO you're going to have to use vba to build criteria using LIKE operator. If the other form fields are going to have search terms, then this code would build the criteria like:
    "LIKE '*'" & Forms!frmMyForm.txtNameHere1 & "'*' OR LIKE '*'" & Forms!frmMyForm.txtNameHere2 & "'*' OR LIKE '*'" & Forms!frmMyForm.txtNameHere3

    assuming it is an OR situation and not an AND situation. Also, that is an example of a build where all 3 contain values. If not, the form field references would change accordingly.
    Alternatively you can do this with a query, but AFAIK, you will have to reference each control 2x - once for when it contains a value and one for when it is null, thus a very long criteria with 6 references for 3 fields.

    Or perhaps a messy nested IIF which will be about a yard long, so my least favourite. No doubt my syntax is not correct in the example - probably some misplaced/missing quotes.

    Also see http://allenbrowne.com/ser-62.html
    Last edited by Micron; 07-20-2021 at 02:36 PM. Reason: added comment & link
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some ideas on keyword searching from this older thread "A TRUE Keyword Search".
    The OP was trying to find books/references based on words or fragments of words in Titles of publications/text books.

  8. #8
    lhanley22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2021
    Posts
    7
    Quote Originally Posted by ssanfu View Post
    Consider posting your dB. I am wondering about your table designs.
    The "Keyword" field violates the rules of normalization. I would have the "Keyword" field values in their own table.

    Please note that "Date" and "Year" are reserved words (and built in functions) in Access.


    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.

    "Cat #" and "Project #" - bad names because if the space and the special character (the hash mark)"
    "Job Title" and "Main Feature" - has spaces
    Thank you. I am going to change my field names just to be sure. I do have the keyword field values in their own table, but could not figure out how to search on them in a form if I was dealing with two tables. I ended up converting the keywords from the lookup field they were in, to just long text (as a field on the database) and am able to search on them using some wild card functions. This works good enough for what I need I think. The only problem is that I have made a "New Entry Form" where users can add entries and put all of their own info in. I'd like them to be able to use a list box in order to populate those keywords, but I want those keywords to show up as long text so they can be searched using my search. Any idea there?

  9. #9
    lhanley22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    IMO you're going to have to use vba to build criteria using LIKE operator. If the other form fields are going to have search terms, then this code would build the criteria like:
    "LIKE '*'" & Forms!frmMyForm.txtNameHere1 & "'*' OR LIKE '*'" & Forms!frmMyForm.txtNameHere2 & "'*' OR LIKE '*'" & Forms!frmMyForm.txtNameHere3

    assuming it is an OR situation and not an AND situation. Also, that is an example of a build where all 3 contain values. If not, the form field references would change accordingly.
    Alternatively you can do this with a query, but AFAIK, you will have to reference each control 2x - once for when it contains a value and one for when it is null, thus a very long criteria with 6 references for 3 fields.

    Or perhaps a messy nested IIF which will be about a yard long, so my least favourite. No doubt my syntax is not correct in the example - probably some misplaced/missing quotes.

    Also see http://allenbrowne.com/ser-62.html
    Thanks for your help. I ended up using the like operator to search on the keyword long text. This works fine enough for what I need.

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

Similar Threads

  1. Searching a table based on multiple fields
    By sam1603 in forum Queries
    Replies: 1
    Last Post: 10-17-2017, 04:48 AM
  2. Replies: 6
    Last Post: 03-03-2017, 09:01 AM
  3. Replies: 1
    Last Post: 10-01-2016, 04:10 PM
  4. Q: Searching Multiple Data
    By spideynok in forum Access
    Replies: 2
    Last Post: 03-19-2012, 07:15 PM
  5. Replies: 4
    Last Post: 09-22-2010, 01:47 AM

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