Checkbox qry into Report

[CD27]

Hello group,
I need help on the following. I have a table with 8 different columns
all of them are checkbox options (e.g. yes or no). I need to run a
report which sums the yes and no.

So it would look something like:

JOB TITLE
Yes XXXX number
No XXXX number

JOB TITLE 2
Yes XXXX number
No XXXX number

JOB TITLE 3
Yes XXXX number
No XXXX number

So far, I create a crosstab qry but can only do one field at a time

TRANSFORM Count(CF.[JOB TITLE]) AS [CountOfJOB TITLE]
SELECT CF.[JOB TITLE]
FROM CF
GROUP BY CF.[JOB TITLE]
PIVOT CF.[JOB TITLE];


How can I create a qry that takes all of the fields into
consideration, so I can make a report off of it.

Thanks

 

[Evi]

You can add the yes/no fields in your query
CountYes: Abs([Field1]=True) + Abs([Field2]=True) + Abs([Field3]=True) etc
Then
CountNo: Abs([Field1]=False) + Abs([Field2]=False + Abs([Field3]=False)

 

[CD27]

Thanks Evi and Marsh,

You are absolutely right Marsh, this table is not normalized at all
but unfortunately i can not do anything about it since I did not
create it. Will see how much trouble this will cause later, I'm
assuming a lot though!

I need help on the following. I have a table with 8 different columns
all of them are checkbox options (e.g. yes or no).  I need to run a
report which sums the yes and no.

So it would look something like:

JOB TITLE
Yes XXXX number
No  XXXX number

JOB TITLE 2
Yes XXXX number
No  XXXX number

JOB TITLE 3
Yes XXXX number
No  XXXX number
[]
How can I create a qry that takes all of the fields into
consideration, so I can make a report off of it.

If the check boxes are so closely related that counting the
yeses makes sense, then you have an unnormalized table
structure that will cause more than this on problem.  You
should put that info in a separate table with only one
yes/no field, a foreign key to the main table and a value to
indicate which option was checked.

You current question can be done by using these odd kind of
expressions:

Yes             =-chk1-chk2-chk3- ...
No              =8+chk1+chk2+chk3+ ...

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -