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
|
|||
|
|||
Option groups, radio buttons and reports...oh my!!
Hello all!
I am up the creek and need your assistance asap! I have created an access database with a survey form that contains 15 option groups each with 4-10 radio buttons. I need to be able to print reports based on what is returned. My issues a a) for the smaller option groups, instead of printing 1, 2, etc. for my returned value, I need it to state for instance the actual city name on the report (because 1=Las Vegas, 2=New Jersey, etc.) and then group/sort in the report according to that city name (all records with Las Vegas will have Las Vegas as a subtitle and then the rest of the associated data for each associated record underneath). b) I still need the larger option groups to retain their numbers of course as they are ratings (1-10), but how can I count how many records have rated the service a 7? I really appreciate your help on this. Thanks in advance, La Diva |
#2
|
|||
|
|||
Option groups, radio buttons and reports...oh my!!
In the query for the report join the table that has the city on the number.
Then use the city name instead of number. -- KARL DEWEY Build a little - Test a little "La Diva" wrote: Hello all! I am up the creek and need your assistance asap! I have created an access database with a survey form that contains 15 option groups each with 4-10 radio buttons. I need to be able to print reports based on what is returned. My issues a a) for the smaller option groups, instead of printing 1, 2, etc. for my returned value, I need it to state for instance the actual city name on the report (because 1=Las Vegas, 2=New Jersey, etc.) and then group/sort in the report according to that city name (all records with Las Vegas will have Las Vegas as a subtitle and then the rest of the associated data for each associated record underneath). b) I still need the larger option groups to retain their numbers of course as they are ratings (1-10), but how can I count how many records have rated the service a 7? I really appreciate your help on this. Thanks in advance, La Diva |
#3
|
|||
|
|||
Option groups, radio buttons and reports...oh my!!
Thanks for your reply Karl!
Does this mean I would have to have a separate table containing 2 fields (city and associated number) for each option group? "KARL DEWEY" wrote: In the query for the report join the table that has the city on the number. Then use the city name instead of number. -- KARL DEWEY Build a little - Test a little "La Diva" wrote: Hello all! I am up the creek and need your assistance asap! I have created an access database with a survey form that contains 15 option groups each with 4-10 radio buttons. I need to be able to print reports based on what is returned. My issues a a) for the smaller option groups, instead of printing 1, 2, etc. for my returned value, I need it to state for instance the actual city name on the report (because 1=Las Vegas, 2=New Jersey, etc.) and then group/sort in the report according to that city name (all records with Las Vegas will have Las Vegas as a subtitle and then the rest of the associated data for each associated record underneath). b) I still need the larger option groups to retain their numbers of course as they are ratings (1-10), but how can I count how many records have rated the service a 7? I really appreciate your help on this. Thanks in advance, La Diva |
#4
|
|||
|
|||
Option groups, radio buttons and reports...oh my!!
That is how I would do it.
Maybe others know different way. -- KARL DEWEY Build a little - Test a little "La Diva" wrote: Thanks for your reply Karl! Does this mean I would have to have a separate table containing 2 fields (city and associated number) for each option group? "KARL DEWEY" wrote: In the query for the report join the table that has the city on the number. Then use the city name instead of number. -- KARL DEWEY Build a little - Test a little "La Diva" wrote: Hello all! I am up the creek and need your assistance asap! I have created an access database with a survey form that contains 15 option groups each with 4-10 radio buttons. I need to be able to print reports based on what is returned. My issues a a) for the smaller option groups, instead of printing 1, 2, etc. for my returned value, I need it to state for instance the actual city name on the report (because 1=Las Vegas, 2=New Jersey, etc.) and then group/sort in the report according to that city name (all records with Las Vegas will have Las Vegas as a subtitle and then the rest of the associated data for each associated record underneath). b) I still need the larger option groups to retain their numbers of course as they are ratings (1-10), but how can I count how many records have rated the service a 7? I really appreciate your help on this. Thanks in advance, La Diva |
#5
|
|||
|
|||
Option groups, radio buttons and reports...oh my!!
or do you mean make a conditional statement? If so, what is the correct
format? Does it go in the criteria line? i.e. criteria: 1="Montana", 2="San Francisco", 3="Jersey City" Thanks for your patience...8o| "La Diva" wrote: Thanks for your reply Karl! Does this mean I would have to have a separate table containing 2 fields (city and associated number) for each option group? "KARL DEWEY" wrote: In the query for the report join the table that has the city on the number. Then use the city name instead of number. -- KARL DEWEY Build a little - Test a little "La Diva" wrote: Hello all! I am up the creek and need your assistance asap! I have created an access database with a survey form that contains 15 option groups each with 4-10 radio buttons. I need to be able to print reports based on what is returned. My issues a a) for the smaller option groups, instead of printing 1, 2, etc. for my returned value, I need it to state for instance the actual city name on the report (because 1=Las Vegas, 2=New Jersey, etc.) and then group/sort in the report according to that city name (all records with Las Vegas will have Las Vegas as a subtitle and then the rest of the associated data for each associated record underneath). b) I still need the larger option groups to retain their numbers of course as they are ratings (1-10), but how can I count how many records have rated the service a 7? I really appreciate your help on this. Thanks in advance, La Diva |
#6
|
|||
|
|||
Option groups, radio buttons and reports...oh my!!
If only have a couple of cities you can do it with nested IIF statement in a
calculated field but a table would be a whole lot easier to change/add. City: IIF([CityField] = 1, "Las Vegas", IIF([CityField] = 2, "New Jersey", IIF([CityField] = 3, "Next city", "Unknown"))) -- KARL DEWEY Build a little - Test a little "La Diva" wrote: or do you mean make a conditional statement? If so, what is the correct format? Does it go in the criteria line? i.e. criteria: 1="Montana", 2="San Francisco", 3="Jersey City" Thanks for your patience...8o| "La Diva" wrote: Thanks for your reply Karl! Does this mean I would have to have a separate table containing 2 fields (city and associated number) for each option group? "KARL DEWEY" wrote: In the query for the report join the table that has the city on the number. Then use the city name instead of number. -- KARL DEWEY Build a little - Test a little "La Diva" wrote: Hello all! I am up the creek and need your assistance asap! I have created an access database with a survey form that contains 15 option groups each with 4-10 radio buttons. I need to be able to print reports based on what is returned. My issues a a) for the smaller option groups, instead of printing 1, 2, etc. for my returned value, I need it to state for instance the actual city name on the report (because 1=Las Vegas, 2=New Jersey, etc.) and then group/sort in the report according to that city name (all records with Las Vegas will have Las Vegas as a subtitle and then the rest of the associated data for each associated record underneath). b) I still need the larger option groups to retain their numbers of course as they are ratings (1-10), but how can I count how many records have rated the service a 7? I really appreciate your help on this. Thanks in advance, La Diva |
#7
|
|||
|
|||
Option groups, radio buttons and reports...oh my!!
b) I still need the larger option groups to retain their numbers of
course as they are ratings (1-10), but how can I count how many records have rated the service a 7? The answer to this one depends quite a bit on how you are going to present the information on a report, etc. and how many groups like this that you have. One method is: In a SUM/Group BY type of query, define 10 fields, each with the action of SUM, and more or less in the following pattern: QuestionACntAnsw1: iif([tblname]![OptionGroup1]=1,1,0) QuestionACntAnsw2: iif([tblname]![OptionGroup1]=2,1,0) QuestionACntAnsw3: iif([tblname]![OptionGroup1]=3,1,0) QuestionACntAnsw4: iif([tblname]![OptionGroup1]=4,1,0) etc It is brute force but will work. If this is for a report via the "Report" area there are other ways of doing the same thing I believe within that took specifically. |
#8
|
|||
|
|||
Option groups, radio buttons and reports...oh my!!
I'm afraid that your problems are rather more fundamental than appear
at first sight. It sounds like you've made the common mistake when designing a 'questionnaire' database of using separate columns for the questions. Its what's known as 'encoding data as column headings'. A fundamental principle of the database relational model, 'the information principle' is that data is stored as explicit values at column positions in rows in tables, and in no other way. In a relational database one approach would be to have a table of Questions, a table of Answers and a table QuestionAnswers which models the relationship between them by having two foreign key columns, each referencing the primary key of the Questions and Answers table. In combination these two columns constitute the composite primary key of the table. Consequently each question will be represented by one row in Questions, each answer by one row in Answers and each possible combination of a question and answer by one row in QuestionAnswers. For each respondent's set of answers you need another table, Responses say, with ResondentID, QuestionID and AnswerID columns, the first being a foreign key referencing the primary key of a respondents table, the latter two a composite foreign key referencing the composite primary key of QuestionAnswers. If each respondent can give only one answer to each question then the primary key of this table is a composite one of RespondentID and QuestionID; if a respondent can make multiple answers per question then the primary key is a composite one of all three columns. You might also want to introduce a QuestionCategories table so that you can categorize each question by means of a foreign key column referencing the primary key of Categories. This would help you sort the data for reporting purposes. With a model such as the above querying the database becomes very much simpler as it is simply a matter of joining the tables as necessary and aggregating the relevant data, e.g. to count the number of instances of each answer to each question would require a query grouped by question then answer and counting the rows per answer with COUNT(*). For data input the simplest way would be a subform based on Responses within a single view form based on Respondents. The subform would have combo boxes for question and answer so its just a case of inserting a row for each question answered by selecting from the combo boxes. This is fine if the data is being input by an operator on the basis of paper questionnaire forms completed by the respondent, but less suitable if the respondent is inputting the data directly into the database via an Access form. For this a better solution is to use an unbound form, which can use option groups as you've done, and to read/write the data to the table in code behind the form. This is not difficult but does need some knowledge of writing VBA code using either DAO or ADO. If you don't want to design your own from scratch you can download an application (Duane Hookom's 'At Your Survey') which demonstrates how to design your own survey database from: http://www.rogersaccesslibrary.com/f...osts.asp?TID=3 It includes sample surveys and a brief manual. Ken Sheridan Stafford, England On Feb 19, 7:27 pm, La Diva La wrote: Hello all! I am up the creek and need your assistance asap! I have created an access database with a survey form that contains 15 option groups each with 4-10 radio buttons. I need to be able to print reports based on what is returned. My issues a a) for the smaller option groups, instead of printing 1, 2, etc. for my returned value, I need it to state for instance the actual city name on the report (because 1=Las Vegas, 2=New Jersey, etc.) and then group/sort in the report according to that city name (all records with Las Vegas will have Las Vegas as a subtitle and then the rest of the associated data for each associated record underneath). b) I still need the larger option groups to retain their numbers of course as they are ratings (1-10), but how can I count how many records have rated the service a 7? I really appreciate your help on this. Thanks in advance, La Diva |
Thread Tools | |
Display Modes | |
|
|