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

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 a 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


--
HTH

Dale

"Amit" wrote in message
...
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 a 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

"Dale Fye" wrote:

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

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



--
HTH

Dale

"Amit" wrote in message
...
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 a 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

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

"Amit" wrote in message
...
"Dale Fye" wrote:

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

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



--
HTH

Dale

"Amit" wrote in message
...
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 a 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

You might need to normalize your data using a union query. Then create a
totals query based on the union query.

In the future, you might want to review "At Your Survey" at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane to see
how to build a normalized survey application.

--
Duane Hookom
MS Access MVP
--

"Dale Fye" wrote in message
...
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

"Amit" wrote in message
...
"Dale Fye" wrote:

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

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



--
HTH

Dale

"Amit" wrote in message
...
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 a 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