Search query across multiple tables, ignoring null fields?

[AlexSalvadori]

I have a database centered on Employees, there are various other tables including Qualifications, job roles, availability etc. And I have been asked to create a search form that will allow the user to find a list of all the employees who meet any number of criteria, ie from the other tables (as specified in the form).



The form is rigged up and ready to go, the problem is that the query isn't returning the results I want it to. It seems that because I've queried fields from all kinds of different tables, it's only returning records for employees who have entries in all of the queried tables. This means that unless an employee has something to do with every table in the query, they are ignored as if they don't exist.

For example:
Emp'Details >>> Job Role >>> Qualification >>> Availability
Ben H >> Data analyst >>A level Math >> North-East
Steve D >> Data analyst >> - >> North-East
Amy C >> Editor >> A level English >>South-east

Using my current query, if I search for employees based on those with the the job role 'Data Analyst' and availability in 'North-East', I would only get Ben H in the results screen, despite the fact Steve D is also a data analyst available in the northeast. So far as I can tell, he doesn't appear because he doesn't have any qualifications on record.

Is there a way to stick an 'or IsNull' to the query field name? (Not the criteria) Or is there another way to make the query ignore null table entries?

If it helps here is the code I am using.

SELECT DISTINCT [TBL Employee Details].*FROM [TBL Training Qualifications] INNER JOIN (([TBL Project Types] INNER JOIN ([TBL Power Stations] INNER JOIN (([TBL Sites] INNER JOIN (([TBL Role/Trades] INNER JOIN (([TBL Other Companies] INNER JOIN (([TBL Availability Areas] INNER JOIN ([TBL Employee Details] INNER JOIN [rTBL Employees-Availability] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Availability].[E-A FKEmployeeID]) ON [TBL Availability Areas].AvaAreID = [rTBL Employees-Availability].[E-A FKAvailabilityID]) INNER JOIN [rTBL Employees-Other Companies] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Other Companies].[E-C FKEmployeesID]) ON [TBL Other Companies].OthComID = [rTBL Employees-Other Companies].[E-C FKOtherCompaniesID]) INNER JOIN [rTBL Employees-Roles] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Roles].[E-R FKEmployeeID]) ON [TBL Role/Trades].RolTraID = [rTBL Employees-Roles].[E-R FKRolesID]) INNER JOIN [rTBL Employees-Sites] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Sites].[E-S FKEmployeeID]) ON [TBL Sites].SiteID = [rTBL Employees-Sites].[E-S FKSiteID]) INNER JOIN [rTBL Employees-Stations-Projects] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Stations-Projects].[E-P-S FKEmployeeID]) ON [TBL Power Stations].PowStaID = [rTBL Employees-Stations-Projects].[E-P-S FKPStationID]) ON [TBL Project Types].ProTypID = [rTBL Employees-Stations-Projects].[E-P-S FKProjectID]) INNER JOIN [rTBL Employees-Training] ON [TBL Employee Details].EmpDetID = [rTBL Employees-Training].[E-T FKEmployeeID]) ON [TBL Training Qualifications].TraQuaID = [rTBL Employees-Training].[E-T FKTrainingID]
WHERE ((([TBL Role/Trades].TraName) Like "*" & [Forms]![iFRM All Search]![Role] & "*") AND (([TBL Training Qualifications].QuaName) Like "*" & [Forms]![iFRM All Search]![Qualification] & "*") AND (([TBL Availability Areas].AreName) Like "*" & [Forms]![iFRM All Search]![Availability] & "*") AND (([TBL Power Stations].StaName) Like "*" & [Forms]![iFRM All Search]![PowerStation] & "*") AND (([TBL Other Companies].ComName) Like "*" & [Forms]![iFRM All Search]![OtherCompanies] & "*") AND (([TBL Sites].SiteName) Like "*" & [Forms]![iFRM All Search]![Site] & "*"));

I'd really appreciate any help on this, I've been wracking my brains for days, internet geniuses, you are my only hope!

[JoeM]

Essentially, if you do not want any employees dropped from your query, you want to change your joins so you are doing LEFT JOINs from your main employee table to all other tables (INNER JOINs only returns matches, that is, records where that person is found in EVERY single table). A LEFT JOIN will return everyone from the table it is coming FROM, and all the matched records (and nulls for unmatched records), so it does not drop anyone from your Main table (unless you have criteria to eliminate some). You can handle the nulls from the unmatched records with the NZ function.

See here for a write-up on join types: http://office.microsoft.com/en-us/ac...010096320.aspx

[AlexSalvadori]

Hi JoeM, thanks for the reply. What should I do for the tables which have many to many relationships? left join to the relationship table, and then left again to the other table? Any chance you have an example you could give me?

I think I understand the theory but I can't get it to work just yet, it keeps telling me the sql couldn't be executed because of 'ambiguous joins', and I don't know how to force one to be performed first...

thanks

[JoeM]

Which tables have your many-to-many joins?
Can you post a view of the relationships between all your tables?

[AlexSalvadori]

Can do, they're actually all many to many relationships, it was intended to add more fields to the other tables as the database grows...
The query looks like a messier version of the above, I am currently searching by the fields TraName, QuaName, AreName, StaName, ComName and SiteName, with all Employee details on show, and the others hidden (this was so I wouldn't get thousands of entries for each user...)

[JoeM]

So are you able to just make all the relationships between the "TBL Employee Details" table and six "rTBL..." tables LEFT JOINS, and leave the rest "as is", or does it balk at that?

[AlexSalvadori]

Do you mean in the actual relationships or just in the query? Having tried both, changing the actual relationships seemed to make no difference, changing the query ones resulted in this message (when i tried to run the query)

I hate error messages...

[JoeM]

Well you could try breaking it up into multiple queries, like it suggests, i.e. perform all the Left Joins first.
Then create a new query based on that first queries and do all the other joins.

I am not totally convinced that your design or approach is recommended way to go about things, but without seeing your tables and data, I really can't say for sure.

[AlexSalvadori]

I'm not really sure how to go about that... but I'll give it a go! I'm not really an Access expert (shocking, I know) so if you think I should do it differently I'm very open to suggestions

[AlexSalvadori]

Is there a way to ask the query not to search for a field if the search box has been left blank? eg some kind of 'Iff false' statement?

[JoeM]

Is there a way to ask the query not to search for a field if the search box has been left blank? eg some kind of 'Iff false' statement?

Here are a few ways:
http://www.dbforums.com/microsoft-ac...eft-blank.html
http://www.databasedev.co.uk/blank_query_parameter.html

[AlexSalvadori]

Thanks for the help I'll see how it goes