I'm not sure why you say "each question being on a separate layout" -- if true, this sounds as if your data table is not normalized. If so, you probably do have to use a separate layout for each question, unless you do the right thing which is to go back in an properly normalize your data.
Normalization is a fairly difficult concept to understand, but here's a way to tell if your data is normalized:
- If a table has fields named like this: Question01, Question02, Question03, or email1, email2... or you are using repeating fields, then your data is probalby not normalized.
The trick for normalization is to avoid building the number of the item into the field name itself, instead you need to have that as a separate field.
The reason I need to have each question's histogram on a separate layout is that each question must have its records sorted by that question, so all the 1's, 2's, 3's, etc. are grouped together. Then the chart can show the Count of each of the 1's, 2's, 3's, etc. which gives me the histogram. Since each layout needs its own sort for a particular question, I need many layouts, one per question.
Can you be more specific as to how your tables are designed? What are the field names and how is the data stored (by row, by column, etc?)
Each response is its own Number field, e.g.
and for each i have a Summary field:
rate_1_1 Count = Count of rate_1_1
Not sure what you mean by 'stored by row or column'...
If I were approaching this problem from scratch, here's what I would do:
Make a table called "Questions" which has an ID an question text, like this:
- ID QuestionText
- 1 How did you like the food?
- 2 Was your waiter friendly?
- 3 ... etc...
Then I'd have a Data table that recorded, for each person surveyed, the question ID and their answer, like this:
- PersonID QuestionID Response
- 1001 1 4
- 1001 2 5
- 1001 3 6
Set up a relationship between the Questions and Data tables (Questions::ID = Data::QuestionID)
Wit this properly normalized relationship, you can then easily build a single layout that does histograms for all your data, just sort by QuestionID, PersonID, Response
I'd also probably set up an "Answers" table which is just a simple reference table that maps the Likert numbers to descriptions, e.g.
- AnswerNumber AnswerText
- 1 Very Litte
- 2 Not very much
- 3 Medium
- 4 A lot
- 5 Very much
(or whatever values you are using...)
That's a good solution. Thank you. Perhaps I can create a table that takes the existing questionnaire data and generates your Data table above.
Thanks for exaplaining. As I suspected, you've got a fully un-normalized data table. This is a perfect example that illustrates the answer to the question "Why should I bother with normalization?"
If it's not too late, I strongly suggest you learn about normalization and redesign your tables & relationships into 3NF, BNF or 4NF or something approaching that.
If it's too late, then I think you'll have to just deal with it some other way, such as
- making dozens of layouts, one for each question (the pain of this will be a good life lesson) :-)
- reorganize your data back into a normalized format. You can probably do this with some clever use of scripting and the go to next field function - basically you'd copy your data out of the existing format and into a format more like I've described in my other post.
Yes, I think that's a good approach. I would do this one of two ways:
- Put all the fields you need to pull data from on a layout, and make sure they are in proper order (rate_1_1, rate_1_2). Using a looping script, use the "Go to Next Field" command to move the focus from field to field, and "Copy" and "Paste" to copy & paste data to your other table. This technique works even if your field names aren't regular.
- If your fields are nicely named (rate_1_1, rate_1_2...) then you might try a script that builds the field name using variables, and then used the Evaluate() function to get the field data based on it's name. E.g. SetVariable $fieldName = "rate_" + $i + "_" + $j) where $i and $j are variables that you increment in a loop inside a loop.