Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    Unanswered: Help with search query

    Hello, I'm new to this forums.
    I have come seeking help in creating a particular search query in Microsoft access.
    I have 4 tables in my database:

    Predicate Table
    PredID| Predicate| SentID
    "1" | "Have" | "1"

    Sentence Table
    SentID| SentencesSubject| ObjectID| Sentence
    "1" | "Hawaii" | "1" | "Hotels in Hawaii that have scuba"

    Object Table
    ObjectID| DirectObject
    "1" | "Scuba"

    Synonymous Table
    SynID | PredID |Syn1 | Syn2| Syn3| Syn4
    "1" | "1" |"Include" | "Involve" | "Offer" |"Contain"
    This is the current query I have:
    SELECT Sentences.Sentence
    FROM (Objects INNER JOIN (Predicates INNER JOIN Sentences ON Predicates.SentID = Sentences.SentID) ON Objects.ObjectID = Sentences.ObjectID) INNER JOIN Synonyms ON Predicates.PredID = Synonyms.PredID
    WHERE (((Predicates.Predicate) Like ["Enter Predicate"]) AND ((Sentences.SentencesSubject) Like ["Enter Subject"]) AND ((Objects.DirectObject) Like ["Enter Object"]));
    Now what the database does is asks the user for 3 inputs (Predicate, Subject and Object) in order to display a result (Sentences). I have that part working.
    Now my issues is, lets say the user instead of inputting the Predicate "Have" he instead inputs one of the Synonymous "Include, Involve etc." Then the user should still get the same result (Sentence) if he had input the original Predicate instead of one of the synonymous. I have been trying to work this out for the past 8 hours and no luck. I've tried Google to look for the answer but no luck.

    So it would be great if one of you could help me out, that would be really great.

    Thank you (I hope I have clearly stated the issue and made it easy to understand, if not feel free to ask questions.)

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so write the query on the fly, only includeing the terms that the user specifies
    personally I prefer handling this sort of problem using a data entry form which verifies the user supplied parameters are 'sane' / valid. then pass those details to your reporting switchboard / menu and pull the values for the query from that switchboard/menu

    you cna pass values between forms / reports / queries by explicitly defining the varaiable or control. these can either be 'pushed' from the data capture form to another or pulled from the data capture form.

    to push values..
    the target form / report has to be already open
    forms!targetformname!targetcontrolname = thiscontrol.value
    to pull values (which is my preferred stylistic)
    the datacapture form has to be open
    in the target form
    myvariablename = forms!datacaptureformname!controlname.

    as an overview
    fromt eh report switchboard
    the user select the datacapture form, it opens with sane defaults pulled from the switchboard
    the datacapture form validates the user input (checks for sane values / good dates and so on)
    the datacapture form has on OK & cancel button
    if the user presses OK the values from the datacaptrue form are pushed to controls on the switchboard, if they press cancel nothing is posted back.
    the user then selects wahtever reports / forms they want with those filter criteira.
    the query is written on the fly
    strWhereClause = "Where 1=1 "
    if not isnull(mynumericcontrol) then 'we are assuming its got valid data in int
    strWhereClause = strWhereClause & " AND anumericcolumn = " & mynumericcontrol
    if not isnull(mystringcontrol) then 'we are assuming its got valid data in int
    strWhereClause = strWhereClause & " AND astringcolumn = '" & mystringcontrol & "'"
    'note the ' encapsualtes the string literal from mystringcontrol
    if not isnull(mydatecontrol) and isdate(mydatecontrol) then 'we are assuming its got valid data in int
    strWhereClause = strWhereClause & " AND adatecolumn = #" & format(mydatecontrol,"mm/dd/yyyy" & "#"
    'note the # encapsualtes the string literal from mystringcontrol
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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