If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
"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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sub Query Consolidation, Syntax Question | Quizarate | Running & Setting Up Queries | 5 | September 9th, 2004 10:30 PM |
Query to join records form 2 databases | bdehning | General Discussion | 5 | August 9th, 2004 03:09 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |
Query combining multiple records from one table can't add records | Clint Marshall | Running & Setting Up Queries | 4 | July 8th, 2004 01:25 PM |