I have downloaded survey questions from a survey-generating program using a web-based server. Each question's answer option, however, was downloaded separately such that, in an Excel file, each answer option was the header and the cells for each record were either "null" or "1".
Ex. of format:
In survey - (question) "What is your favorite color?"
In survey - (answers listed below, in a multiple choice fashion) "Blue", "Green", "Red", "Yellow"
In Excel - (header columns) "What is your favorite color? - Blue", "What is your favorite color? - Green", "What is your favorite color? - Red" etc.
I need to consolidate those Excel header columns such that they will be grouped under one, single question type ("What is your favorite color?") and that each answer option can be included as a type of that question. To be more clear, if I were to drag "What is your favorite color" into a column or row, "Blue", "Green", "Red", and "Yellow" would appear.
Right now, I can only list each Excel header column for each answer option, which then displays "null"/"1". I cannot perform any Measures on these because it only considers whether it's a "null" or "1" value. Even if I were to disable "null" in the Measure, I could not perform any calculations like SUM or COUNT of the "1s" in the entire data source (the Excel sheet's records of each person who answered).
I was thinking, is there a way for me to create an Access database that would group the Excel header columns under a table called "What is your Favorite Color?"/"Favorite Color" and re-label each so that each table entry will read the color type (Blue, Green, Red, etc.), instead of "What is your favorite color? - Blue", etc.
And if that is possible, how do I properly relate the two databases when the Access will be addressing the answer options as one type, and Excel will be addressing them as four, different types?
Column H isn't labeled in data. Is that a typo? Also, there are some who seem to have more than 1 favorite color. Is that how your data works? i.e. is it mark all that apply or select exactly one value? In that case it is more like a series of True False questions than a single multiple choice one, which would explain how your data came to be structured in this way.
