Query based on yes/no boxes

B

bassplayer

Hi
I have a database for storing data from incoming referral forms
These referral forms have 7 different yes/no boxes. I want to run
query that tells me how many referrals have only 1 yes/no box ticked
how many have 2 yes/no boxes ticked, how many have three and so on
Any help on this would be greatly appreciated

Thank
 
G

Gary Walter

bassplayer said:
I have a database for storing data from incoming referral forms.
These referral forms have 7 different yes/no boxes. I want to run a
query that tells me how many referrals have only 1 yes/no box ticked,
how many have 2 yes/no boxes ticked, how many have three and so on.
Any help on this would be greatly appreciated.
Click to expand...
In the background, your yes/no fields store

0 for "no"
-1 for "yes"

so if you add the 7 fields and get -1,
only one was ticked
for that record..

if get -2,
only two were ticked
for that record...

etc..

Hopefully that will get you going...

good luck,

gary
 
G

Gary Walter

To be more specific...

SELECT
Count(*) As ReferralCnt,
Abs(f1+f2+f3+f4+f5+f6+f7) As TickCnt
FROM
yourtable
GROUP BY
Abs(f1+f2+f3+f4+f5+f6+f7);

{replace "fx" with real name
of your yes/no fields
and "yourtable" with real name
of your table}

good luck,

gary
 
M

[MVP] S.Clark

Ugh.

Create a query that counts the number of checked (true) columns.

Select FieldID, iif(f1,1,0) + iif(f2,1,0) + iif(f3,1,0) + iif(f4,1,0) +
iif(f5,1,0) + iif(f6,1,0) + iif(f7,1,0) as Cnt

In another query, reference the first query to apply criteria like Cnt <= 4

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
Top