Help with Searching Records

[lushh]

hi.. i am currently creating an employee database.. and there is a form on
the database that needs to search on records.. i am planning to create a form
with a single textbox (for the keyword entry) and a search command button
that when clicked, the search results will be shown in a seaparate continuous
form.

now here is my query:

SELECT tblEmployee.EmployeeIDPK, tblEmployee.LastName, tblEmployee.FirstName,
tblEmployee.MiddleName, tblCompany.CompanyName, tblDealer.DealerName,
tblOutlet.OutletName, tblPosition.PositionName
FROM (tblOutlet INNER JOIN (tblDealer INNER JOIN (tblCompany INNER JOIN
tblPosition ON tblCompany.CompanyIDPK = tblPosition.CompanyIDFK) ON tblDealer.
DealerIDPK = tblPosition.DealerIDFK) ON tblOutlet.OutletIDPK = tblPosition.
OutletIDFK) INNER JOIN tblEmployee ON tblPosition.PositionIDPK = tblEmployee.
PositionIDFK
WHERE (((tblPosition.PositionName)=[Forms]![frmSearchEmployee]![txtSearchFor])
) OR (((tblOutlet.OutletName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR
(((tblDealer.DealerName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblCompany.CompanyName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.MiddleName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.FirstName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.LastName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.EmployeeIDPK)=[Forms]![frmSearchEmployee]![txtSearchFor]));

but when i click search, the results are empty. what could be wrong with the
query? is it the query? what should be the record source for the search form
and the result form?

if you want to see the actual ms access file, here it is:
http://www.gigafiles.co.uk/files/636/human resource info system_2006-10-11.zip

the name of the form is frmSearchEmployee and frmSearchResultEmployee and the
name of the query is qrySearchResult.. thanks a lot and God bless..

 

[PeterF]

Your query uses inner joins, this means there has to be a matching record for every table in order to give results. If you change the joins to right joins you will get a result.

I'm not sure if all relationships are correct but this seems to work:

SELECT tblemployee.employeeidpk, 
       tblemployee.lastname, 
       tblemployee.firstname, 
       tblemployee.middlename, 
       tblcompany.companyname, 
       tbldealer.dealername, 
       tbloutlet.outletname, 
       tblposition.positionname 
FROM   (tbloutlet 
        RIGHT JOIN (tbldealer 
                    RIGHT JOIN (tblcompany 
                                RIGHT JOIN tblposition 
                                  ON tblcompany.companyidpk = tblposition.companyidfk) 
                      ON tbldealer.dealeridpk = tblposition.dealeridfk) 
          ON tbloutlet.outletidpk = tblposition.outletidfk) 
       RIGHT JOIN tblemployee 
         ON tblposition.positionidpk = tblemployee.positionidfk 
WHERE  (((tblposition.positionname) = [forms]![frmsearchemployee]![txtsearchfor])) 
        OR (((tbloutlet.outletname) = [forms]![frmsearchemployee]![txtsearchfor])) 
        OR (((tbldealer.dealername) = [forms]![frmsearchemployee]![txtsearchfor])) 
        OR (((tblcompany.companyname) = [forms]![frmsearchemployee]![txtsearchfor])) 
        OR (((tblemployee.middlename) = [forms]![frmsearchemployee]![txtsearchfor])) 
        OR (((tblemployee.firstname) = [forms]![frmsearchemployee]![txtsearchfor])) 
        OR (((tblemployee.lastname) = [forms]![frmsearchemployee]![txtsearchfor])) 
        OR (((tblemployee.employeeidpk) = [forms]![frmsearchemployee]![txtsearchfor]));