Running query using checkboxes 1

[postmanphat]

Hi,

I have a database that lists staff in an organisation and what their area of expertise is. I want to be able to query the database using checkboxes on a form. There are currently 6 areas of expertise in the tblExtpertise (eg A -F)so I have a form with 6 unbound checkboxes that each relate to an area of expertise.

If I select expertise A on the form, I want the query to return all the staff who have expertise A. If I choose A, B and C, I want the query to return those members of staff whose expertise includes A, B and C.

Table structure as follows:

tblStaff
StaffID*
Name

tblExpertise
ExID*
Expertise

tblStaffEx
ID
StaffID
ExID

I can get it to select people where the checkboxes exactly relate to a staff's expertise. i.e. if I choose A and B, it will bring back the guy who has expertise A and B, but not the guy who has A, B and C. Which unfortunately is no good.

Many many many thanks in advance

Dave

 

[PWise]

This query will return all staff that are in ExID that are checked on the form

Select * from(
select StaffID 
from tblStaffEx
where ExID="a" and forms!checkform!a=true
union
select StaffID 
from tblStaffEx
where ExID="B" and forms!checkform!b=true
......
union
select StaffID 
from tblStaffEx
where ExID="f" and forms!checkform!f=true)dt

 

[PHV]

I can get it to select people
what is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[postmanphat]

Hi PWise...

Tried that code, but its not picking up the criteria from the checkboxes on the form. When I run it the parameter box pops up and I need to put in A to return the (correct) results.

PHV - sorry but I did that in a version I have since deleted, I started again from scratch yesterday on a dummy db and literally all I've got in this test db are the tables described abovee and a form with 6 unbound checkboxes on it.

Thanks

 

[PWise]

did you change the form name and control name to the name of your form anf control?

 

[postmanphat]

Yep, changed it all to point to my form. Played about with it a bit more and screwed it right up. So i re-copied your SQL back into the query, changed the form and control references and now it says it is "Either typed incorrectly or too complex to evaluate".


AAAARRRRRGGGGHHHHHHHHHHH

My table designs haven't changed at all from what is above.

 

[PWise]

Is your form open?

Let us see your SQl

 

[postmanphat]

form is open

SQL:

Select * from(
select StaffID 
from tblStaffEx
where ExID="a" and forms!form1!check0=true
union
select StaffID 
from tblStaffEx
where ExID="b" and forms!form1!check2=true
union
select StaffID 
from tblStaffEx
where ExID="c" and forms!form1!check4=true
union
select StaffID 
from tblStaffEx
where ExID="d" and forms!form1!check6=true
union
select StaffID 
from tblStaffEx
where ExID="e" and forms!form1!check16=true
union
select StaffID 
from tblStaffEx
where ExID="f" and forms!form1!check18=true)dt

 

[postmanphat]

Image of all my tables, relationships and query.

 

[postmanphat]

Thanks for all your help, I've gotta go now but will be back tomorrow!!!

 

[PWise]

When I run it the parameter box pops up and I need to put in A to return the (correct) results.

What does the parameter box say? Because "A" is not a parameter in the query

 

[PHV]

SELECT DISTINCT StaffID
FROM tblStaffEx
WHERE (ExID='A' And [Forms]![Form1]![name of A CheckBox]=True)
OR (ExID='B' And [Forms]![Form1]![name of B CheckBox]=True)
OR (ExID='C' And [Forms]![Form1]![name of C CheckBox]=True)
OR (ExID='D' And [Forms]![Form1]![name of D CheckBox]=True)
OR (ExID='E' And [Forms]![Form1]![name of E CheckBox]=True)
OR (ExID='F' And [Forms]![Form1]![name of F CheckBox]=True)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[postmanphat]

Hi PHV,

Unfortunately your code above still comes back with "This expression is typed incorrectly or is too complex to be evaluated...." error message. All the table, control, and form names are all correct in your code

SELECT DISTINCT StaffID
FROM tblStaffEx
WHERE (ExID='A' And [Forms]![Form1]![check0]=True)
OR (ExID='B' And [Forms]![Form1]![check2]=True)
OR (ExID='C' And [Forms]![Form1]![check4]=True)
OR (ExID='D' And [Forms]![Form1]![check6]=True)
OR (ExID='E' And [Forms]![Form1]![check16]=True)
OR (ExID='F' And [Forms]![Form1]![check18]=True)

 

[PHV]

Where did you type the above code ?
You should type it in the SQL view pane.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[postmanphat]

I did.

I've just put together an even more simplified version of the above database with 2 members of staff and just 2 items of expertise, where staff member 1 has both expertises (is that a word?) and staff member 2 has neither. There is a form with just 2 text boxes and I've obviously adjusted your code and it works. Apart from the simplifying of the database design, there is NO difference whatsoever. I shall now see if I can scale it up for what I want.

Thanks for all your help PHV, have a star!

 

[postmanphat]

Have scaled it aup succesfully and its working(ish) on my database.

The new problem is this:

I have a member of staff (lets call them StaffA) who has expertise in A, B, C and D. On my form I've ticked against expertises D, E and F. The problem is that the query is returning back StaffA just because he has expertise D.

I only want it to bring back people where there are exact matches. I.e. I want the query to return all staff with expertise in D AND E AND F. If a member of staff has all 6 expertises then they should come up too.

 

[JBinQLD]

Rather than just select staffID, select tblStaffEx.* Tht will give you a subset of all staff that match at least one criteria and then add a where clause for the exact tickboxes?

Nice little problem this one mate, I'm old so I wuld have gone VBA long ago. Really enjoying following this

JB

 

[postmanphat]

Glad to see we're keeping you entertained JB!!!!!

I have created a VERY simplified db to work on this and the only other field in tblStaffEx is Name. SQL currently stands at:

SELECT DISTINCT tblStaffEx.StaffID, tblStaff.Name
FROM tblStaff INNER JOIN tblStaffEx ON tblStaff.StaffID = tblStaffEx.StaffID
WHERE
(((tblStaffEx.ExID)=1) AND (([Forms]![Form1]![check2])=True)) OR
(((tblStaffEx.ExID)=2) AND (([Forms]![Form1]![check4])=True)) OR
(((tblStaffEx.ExID)=3) AND (([Forms]![Form1]![check9])=True)) OR 
(((tblStaffEx.ExID)=4) AND (([Forms]![Form1]![check11])=True)) OR
(((tblStaffEx.ExID)=5) AND (([Forms]![Form1]![check13])=True)) OR 
(((tblStaffEx.ExID)=6) AND (([Forms]![Form1]![check15])=True))
GROUP BY tblStaffEx.StaffID, tblStaff.Name;

"then add a where clause for the exact tickboxes?" where would you add this above???

 

[postmanphat]

Sorry JB, completely misread your post, ignore my post above!!!!!!!!!!

 

[postmanphat]

Ok, so THIS is my current SQL:

SELECT DISTINCT tblStaffEx.*
FROM tblStaffEx
WHERE 
(((tblStaffEx.ExID)=1) AND (([Forms]![Form1]![check2])=True)) OR 
(((tblStaffEx.ExID)=2) AND (([Forms]![Form1]![check4])=True)) OR 
(((tblStaffEx.ExID)=3) AND (([Forms]![Form1]![check9])=True)) OR 
(((tblStaffEx.ExID)=4) AND (([Forms]![Form1]![check11])=True)) OR 
(((tblStaffEx.ExID)=5) AND (([Forms]![Form1]![check13])=True)) OR 
(((tblStaffEx.ExID)=6) AND (([Forms]![Form1]![check15])=True))

So where would you "then add a where clause for the exact tickboxes?" in the above SQL?

Thanks!