• Unpredictable SQL results (Access 97)

    Author
    Topic
    #366887

    I’m working on a database that generates resumes from Word and am using a form with nine items the user can choose to query simultaneously. Although Access does generate these queries, query results are unpredictable when more than 4 items are checked, in that Access sometimes throws in a result that shouldn’t be returned. Am I running into some kind of size limit (as in queries can only be a certain length)?

    Here’s an example of an actual query that returned someone who had nothing entered under tblEmployee.Discipline and “Master’s” entered under tblEmployee.HighestDegree:
    SELECT DISTINCT tblEmployee.FirstName, tblEmployee.LastName
    FROM (((((tblEmployee INNER JOIN tblMarketSectors ON tblEmployee.EmployeeID = tblMarketSectors.EmployeeID) INNER JOIN tblSiteExp ON tblEmployee.EmployeeID = tblSiteExp.EmployeeID) INNER JOIN tblProjects ON tblEmployee.EmployeeID = tblProjects.EmployeeID) INNER JOIN tblLanguages ON tblEmployee.EmployeeID = tblLanguages.EmployeeID) INNER JOIN tblQualifications ON tblEmployee.EmployeeID = tblQualifications.EmployeeID) INNER JOIN tblSeniorRoles ON tblEmployee.EmployeeID = tblSeniorRoles.EmployeeID
    WHERE tblMarketSectors.MktSector Like ‘*lng*’ OR tblProjects.[Project Type] Like ‘*lng*’ OR tblProjects.ProjectPara Like ‘*lng*’ AND tblSiteExp.Site LIKE ‘*nigeria*’ AND tblProjects.Client LIKE ‘*nigeria*’ AND tblProjects.Country = “Nigeria” AND tblEmployee.Discipline LIKE ‘*test*’ AND tblLanguages.Language LIKE ‘*french*’ AND tblEmployee.HighestDegree = “Bachelor’s” AND tblQualifications.Qualification LIKE ‘*chemical*’;

    Results returned are correct when individual search items are selected, and so far as I have been able to test, they are correct when any combination of up to four search items are used. After that, it sometimes messes up when five to eight items are selected and it always messes up when all nine are selected. (By the way, in our resumes “Nigeria” does appear as part of client, site, and country field entries. Also, site means the employee actually worked in that country, while country means the employee’s projects were located in that country.)

    Any ideas?

    Viewing 0 reply threads
    Author
    Replies
    • #570389

      What you first might try is to use an alias for the 2 tables, that will make the SQL a little easier to read and may help if length is a problem.
      For example:

      old: tblEmployee.LastName FROM (((((tblEmployee INNER JOIN

      Change to: E.LastName FROM (((((tblEmployee AS E INNER JOIN

      I also had a hard time following all the OR’s and AND’s in your WHERE statement; and perhaps Access interprets it differently than you expect. I’d using parenthesis around the clauses to make it easier to read and avoid any possible confusion.

      • #570398

        You’re right about the ORs and ANDs. Right after I posted this, I mentioned this problem to my boss & he called a programmer friend of his who told me to put parentheses around all the ORs, which I did as follows:

        strWhere = strWhere & ” AND ” + “(tblMarketSectors.MktSector Like ‘*” + txtMarketSector + “*’ OR tblProjects.[Project Type] Like ‘*” + txtMarketSector + “*’ OR tblProjects.ProjectPara Like ‘*” + txtMarketSector + “*’)”

        (The initial AND gets stripped off later.)

        This seems to have fixed it, as these were the only ORs. So far as I can tell with only a little testing, I can now run reliable queries on all 9 items simultaneously.

        Thank you for your help!

    Viewing 0 reply threads
    Reply To: Unpredictable SQL results (Access 97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: