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

 

[JBinQLD]

Sorry, in the evenings i've only got pda with no Access and my brain aint up to it. But how about my other idea of bringing VBA into it? declare your results above as a record set and then iterate through that recordset checking for your form values and inserting postive matched into another table?

It's easier to just read and learn from this one at this time of night! Damn time differences

Good luck and I'll continue to follow with intrigue cos I always quit SQL too early and go VBA.

 

[PHV]

SELECT 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
HAVING Count(*)=Abs(([Forms]![Form1]![check2]=True)+([Forms]![Form1]![check4]=True)+([Forms]![Form1]![check9]=True)+([Forms]![Form1]![check11]=True)+([Forms]![Form1]![check13]=True)+([Forms]![Form1]![check15]=True))

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

 

[postmanphat]

PHV, I've said it before and I'll say it again: I bloomin' love you.

 

[postmanphat]

PHV, would you mind just quickly explaining what you've done please? I've not encountered the 'Abs' statement before.

Thanks again

Dave

 

[PHV]

Abs is a function returning the absolute value of a signed numeric.
A boolean expression evaluates to -1 when true and 0 when false.

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