Multi-table search query to ignore null fields

[XelaIrodavlas]

I have a form & query to search across two related tables 'Employees' and 'qualifications' (Many-many relationship), which semi-successfully finds employees and their qualifications based on what is typed into the form.

The problem is that the form will only return records where all the fields are complete (ie; they have at least one qualification on record.) any employees who don't have any qualifications are so far being ignored completely. For example, if I search 'Salvadori' in the last name box, it will only show my record if I have at least one qualification; if I have none, it acts as if I don't exist...

I would like to fix it so that the query displays employee details despite having no qualifications, preferably without going through every single entry and putting n/a in all the blank fields... eg, substitute all null fields with a * or n/a etc.

The criteria i'm currently using in the query design view is as follows:

Like "*" & [Forms]![SearchForm]![SearchFormTextbox] & "*"

So, any ideas?? Thanks in advance

ps. this is just a simplified version of my query, since the last time I asked about this I got incredibly confused... I'm hoping if I can get my head around this one then the rest will fall neatly into place...

 

[John Big Booty]

It should be a fairly simple matter to use an Update Query to insert "N/A" in the appropriate field.

 

[XelaIrodavlas]

I'm trying to avoid that if possible, there are a lot of fields and I don't want the user to have to enter n/a every time theres a field they want to leave blank..

I've been playing around with the Nz Function but I'm not entirely sure how to work it.. can it be used in the query field name? heres the last thing i tried:

Expr1: Nz([QualificationName],'N/A') so this should change any blank qualifications found by the query to N/A... but it isnt...

 

[pr2-eugin]

I'm trying to avoid that if possible, there are a lot of fields and I don't want the user to have to enter n/a every time theres a field they want to leave blank..

If you are using a Form then you can make use of the Form Before Update to set it to N/A if they leave the field blank.. not really hard to implement in my point of view..

I've been playing around with the Nz Function but I'm not entirely sure how to work it.. can it be used in the query field name? heres the last thing i tried:

Expr1: Nz([QualificationName],'N/A') so this should change any blank qualifications found by the query to N/A... but it isnt...

Yes it can be used in Query.. I have used it a lot of times.. It should set the value to N/A.. not sure why it is not setting it.. Can you show the Query you are using?

 

[XelaIrodavlas]

Sorry this message becomes a bit of a brainstorm session towards the end

In any case, I got the Nz to work

And you're right I can leave it as N/A by default on the forms, good point. But i'm not sure that will actually solve the bigger problem, yes it will fill any gaps where there is missing detail (eg, a qualification ID without a name. it will just become N/A) but it wont help where there is no information at all (ie, the employee has no qualifications). I can't see how that would ever work in the relationship table for instance (employeeID - n/a?) and they're not showing up on the query because of it

I think the problem is in the query, and the results it displays. Is there a way to focus it on employees, and make it show their records regardless of whether they have records in all the other tables, but still display them if they are, all while retaining the searchability?

now that I write it out, that does sound like a tall order... Maybe theres an alternative..:

Would it be easier to have: a form with a list of boxes where you can enter information (such as a qualification name, availability etc) and then click a search button (I already have that bit.), Which will then return a list of all employees (and contact details) who meet the specified criteria? That way I don't actually need to see their qualifications or anything else, just as long as the list of employees is accurate. but this might open another can of worms, the search will need to be able to search for multiple field entries at once (eg, two, three or more qualifications at a time, since we wont be able to browse)

 

[pr2-eugin]

What JOIN operation do you use? If there are two tables for example employeePersonal (PK empID) and employeeEducation (FK eEmpID).. So data is something like..

empID       fName     contactNo            jobPosition
  45         Max         000000000        DB Tester
   8         Andy       1111111111        DB Developer
  78         Chris       2222222222       IT Support

eEmpID       eduDetails    qualName        
45            B.Sc.        Computing
78            B.Sc.        Computing

If you use employeePersonal RIGHT JOIN employeeEducation it only return MATCHING eEmpID from employeeEducation. So result will be..

empID       fName     contactNo            jobPosition    eduDetails    qualName 
  45         Max         000000000        DB Tester          B.Sc.        Computing
   8         Andy       1111111111        DB Developer       B.Sc.        Computing

If you use employeePersonal LEFT JOIN employeeEducation it will return ALL information from employeePersonal (even if no match for them is found)

empID       fName     contactNo            jobPosition    eduDetails    qualName 
  45         Max         000000000        DB Tester          B.Sc.        Computing
   8         Andy       1111111111        DB Developer       B.Sc.        Computing
  78         Chris       2222222222       IT Support

If you have used Inner Join then try changing the type of Join and see what happens..

 

[XelaIrodavlas]

Sorry for the delay, I tried playing around with the joins but it hasn't helped, in any case I have made some progress...

I've now used Select Distinct to cut down the thousand or so records I get back per employee (it only seems to work because I've told the query not to show anything except for fields from the employee details table... On a side note, is there a way to set conditional visibility? eg, if they entered something in the search field then it becomes visible?)

Unfortunately the query is still ignoring all the employees who don't have at least one entry in all the other tables, which is a lot of them :\. Do you think it would it be a good idea to set a default entry for each employee, under the relationship tables? It just doesn't feel right using another ID in each table just for an n/a option, so there must be a better way... Union queries...? I'm confused again

 

[XelaIrodavlas]

I tried setting the default values within the subforms for each other table to a new N/A value, but this only works if you then click the default value and press enter. i need it to automatically create the relationships employee to field :\


is there a way to apply an 'OR IsNull' function to field values in the query? that way I could search for 'qualification or nothing' rather than just qualification or total failure.

 

[John Big Booty]

A Default value will only be applied to new records that are created after the default value is set. To set old records to the Default you will need to run a one time update query to change any Null values in that field to N/A.

 

[XelaIrodavlas]

still no luck :\

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?

 

[XelaIrodavlas]

Finally solved this issue so for the sake of anyone else reading I will explain:

1) Of course using the correct joins is the way to get all employees regardless of whether they have an entry in other tables - my issue was that some of the other tables were already related (thanks to the relationships view in acc 2010). The SQL didn't know which joins to prioritise. So for the sake of this query I deleted the excess joins as they weren't necessary here.

2) Then it's just about using the right statement in the criteria, you can include all the Like "*" stuff if you want to, but essentially all you need is, criteria = [forms]![myform]![mycontrol] OR [forms]![myform]![mycontrol] is null. This then allows you to enter text into any number of the form controls, and will include all records where fields are blank.

3) This does create an unusual problem within the query itself - for some reason when I close it and open it again it creates a very large amount of Or criteria (I suspect one for each possible combination of controls) which crashes the system if you try to view in sql mode. however I found that after repeating steps 1 and 2 - without closing the query, I could go to SQL view, copy the code from there and then copy it directly into the source of whatever form or record i need - without any weirdness going on.

I Hope this is useful to at least someone, as I know what a ball ache this was for me.

Thanks all,