Associating / Mapping Fields to Values using reference code list

jmalenfant — Thu, 31 May 2018 14:22:48 GMT

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

topic Associating / Mapping Fields to Values using reference code list in App Development

Associating / Mapping Fields to Values using reference code list