How to create a query with multiple answers to a question?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

MS Access 2K, Windows XP
====================
Hi,

I have a table with fields for different answers for a question. Eg. The
question is "What age groups does your program serve?". The possible answers
in check-box format are: 1. Preschool, 2. Child, 3. Teen, 4. Adult, 5.
Senior. Since more than one answer can be checked, I created Yes/No fields
corresponding to each answer.

Is there a way to create a query that lists the possible answers to the
question, and the number of records that checked the box for that answer? I
tried creating Cross-Tab query using Wizard, but was unable to accomplish
that. If I select 3 row headings at the first step (corresponding to 3 out
of 5 answers, since that is the limit), and then if I select RecID (which is
the PK in the table), I still have to select one more field to do either
count, average or sum on one of the remaining fields. Can I skip the second
step?

What I'd like is a query that has 2 columns, one for the answers (1. - 5.),
and the other for the number of records that checked "Yes" for that answer.
(Or, 2 rows and 5 columns.) Is this possible to accomplish using one query?

Any help will be appreciated.

-Amit
 
How about something like:

SELECT Question, _
Sum(ABS(Preschool)) as 1_Preschool, _
Sum(ABS(Child) as 2_Child, _
Sum(ABS(Teen)) as 3_Teen, _
Sum(ABS(Adult)) as 4_Adult, _
Sum(ABS(Senior)) as 5_Senior
FROM yourTable
WHERE Question = 1
GROUP BY Question
 
Dale Fye said:
How about something like:

SELECT Question, _
Sum(ABS(Preschool)) as 1_Preschool, _
Sum(ABS(Child) as 2_Child, _
Sum(ABS(Teen)) as 3_Teen, _
Sum(ABS(Adult)) as 4_Adult, _
Sum(ABS(Senior)) as 5_Senior
FROM yourTable
WHERE Question = 1
GROUP BY Question
Click to expand...

That won't work, because I do not have a field for "Question". I only have
fields for the answers, and each field is of "Yes/No" type. I just want to
group the fields that are answers for that question, and get the number of
"Yeses" for each answer. I just wasn't sure if this could be done using a
single query. Thanks though.

-Amit
 
Can you give me your data structure and an example (several records worth of
data), followed by what you want the output to look like given the data
sample you provided.

Dale
 
Back
Top