Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I have a question about using mapping tables to create a data model to capture survey data.
I'm working with 3 Excel files (examples below and excel doc attached). Basically I need to use these data and mappings to produce a chart that allows me to see the survey data with the text for both the question and answers by person/respondent and aggregate the results by question.
I've done a lot of reading on mappings and have tried transposing the tables to have the columns match but I can't figure out how to actually link all 3 tables. The result file is giving me issues. Any help is GREATLY appreciated 
Thanks!
1. Questions in survey with 2 columns Question_ID and Question_Text:
| Question_ID | Question_Text |
| 1A | Are you interested in receiving information on the following topics? |
| 1A1 | Alcohol and other drug use |
| 1A2 | Cold/Flu/Sore throat |
| 1A3 | Stress |
| 1A4 | Eating disorders |
| 1A5 | Grief |
| 1A6 | Injury prevention |
| 1A7 | Nutrition |
| 1A8 | Physical activity |
| 2 | On how many of the past 7 days did you get enough sleep so that you felt rested when you woke up in the morning? |
| 3 | What is your status? |
| 4A1 | Have you ever: |
| 4A2 | Felt things were hopeless |
| 4A3 | Felt overwhelmed by all you had to do |
| 4A4 | Felt exhausted (not from physical activity) |
| 4A5 | Felt very lonely |
| 4A6 | Felt very sad |
2. Response Options with 3 columns -Question ID, Response Code, Response Text:
| Question_ID | Question_Response_Value_Code | Question_ResposeValue_Text |
| 1 | 1 | No |
| 1 | 2 | Yes |
| 2 | 1 | 0 days |
| 2 | 2 | 1 day |
| 2 | 3 | 2 days |
| 2 | 4 | 3 days |
| 2 | 5 | 4 days |
| 2 | 6 | 5 days |
| 2 | 7 | 6 days |
| 2 | 8 | 7 days |
| 3 | 1 | Full-time |
| 3 | 2 | Part-time |
| 3 | 3 | Other |
| 4 | 1 | No, never |
| 4 | 2 | No, not in the last 12 months |
| 4 | 3 | Yes, in the last 2 weeks |
| 4 | 4 | Yes, in the last 30 days |
| 4 | 5 | Yes, in the last 12 months |
3. Response Options with 3 columns -Question ID, Response Code, Response Text:
Example of 15 survey responses (1 row per survey)
| 1A1 | 1A2 | 1A3 | 1A4 | 1A5 | 1A6 | 1A7 | 1A8 | 2 | 3 | 4A1 | 4A2 | 4A3 | 4A4 | 4A5 | 4A6 |
| 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 1.00 | 6.00 | 1.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 |
| 1.00 | 1.00 | 1.00 | 2.00 | 2.00 | 2.00 | 2.00 | 1.00 | 8.00 | 1.00 | 4.00 | 4.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 8.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
| 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 4.00 | 1.00 | 3.00 | 5.00 | 2.00 | 2.00 | 2.00 | 5.00 |
| 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 6.00 | 1.00 | 3.00 | 3.00 | 4.00 | 4.00 | 4.00 | 5.00 |
| 2.00 | 2.00 | 1.00 | 2.00 | 2.00 | 1.00 | 2.00 | 2.00 | 3.00 | 1.00 | 5.00 | 5.00 | 5.00 | 5.00 | 2.00 | 5.00 |
| 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 5.00 | 1.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 4.00 |
| 2.00 | 2.00 | 1.00 | 2.00 | 2.00 | 2.00 | 1.00 | 2.00 | 4.00 | 1.00 | 3.00 | 3.00 | 2.00 | 3.00 | 3.00 | 3.00 |
| 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 8.00 | 1.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 | 4.00 |
| 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 3.00 | 1.00 | 3.00 | 3.00 | 3.00 | 4.00 | 5.00 | 3.00 |
| 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 3.00 | 1.00 | 2.00 | 2.00 | 2.00 | 2.00 | 1.00 | 1.00 |
| 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 |
| 2.00 | 2.00 | 2.00 | 2.00 | 2.00 | 1.00 | 1.00 | 1.00 | 4.00 | 2.00 | 4.00 | 2.00 | 1.00 | 1.00 | 1.00 | 4.00 |
| 1.00 | 2.00 | 2.00 | 2.00 | 2.00 | 1.00 | 1.00 | 2.00 | 1.00 | 1.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 | 3.00 |
| 1.00 | 2.00 | 1.00 | 2.00 | 2.00 | 2.00 | 2.00 | 1.00 | 5.00 | 1.00 | 5.00 | 5.00 | 1.00 | 1.00 | 1.00 | 5.00 |
