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?