Hey guys was hoping I could get some help on a data set I'm working with. I've put together a pretty thorough explanation so please bear with me! I've tried several different routes now but haven't been able to get the desired result. As a note, I am using
The Set Up/Structure
- The “Survey ID” table assigns each single survey a number. The first survey taken is number 1, and the 100th survey taken is number 100.
- Each number in this table represents an individual survey consisting of questions and answers
- For the sake of this example, there were 100 surveys taken
- The “Question” list contains a list of all possible questions
- For the sake of this example, there were 10 questions
- The “Answer” list contains a list of all possible answers
- For the sake of this example, there were 3 possible answers: Yes, No, and Maybe
- The “QA Reference” table is the intersection between questions and answers
- If you answered Question 1 with Answer 1, it would have a value “11”
- The “Survey Response” table is the intersection of all data: Survey ID, QA Reference, Questions, and Answers
- Note all of this information is stored in MS Access. I am using a live connection and have created all of the Joins.
The Desired Result: Filtering
I would like to filter my data in such a way that I can track how many people who answered Question 1 with a “Yes” answered Questions 5 and 6 with a “No”…. This means, I need to somehow store the Survey ID which uniquely identifies a person’s survey response. Meaning, if I filter on People who answered Question 1 with “Yes”, I want to see how the people who answered Question 1 with “Yes” answered Questions 2-10.
Logic: “If Answer to Question 1 is Yes, store the list of Survey IDs. Given this list of Survey IDs, how were the rest of the questions answered?”
The Issue: Data is Referenced
Since all answers are being referenced from the Answer Table, I’m finding it difficult to filter. If I filter Question 1 to see how many participants answered Question 1 with “Yes”, I can no longer see the response “Yes” for any other questions. I’ve been using the Quick filter to filter my “Questions.”
Ideally when I make a selection, I am selecting a list of Survey IDs which correspond to that question.
Possible Solution: Have a new table (Storage Table) which can store the Survey ID’s applicable to my filter. This would be a dynamic table that changes based on my filter selection. Is this possible?
Do I need to create a new connection to the same data so that I can independently filter the data set w/o influencing the rest of the data?
Any and all help is greatly appreciated! Sorry for the extremely detailed description
The data is a sensitive so if I'm hoping this will suffice but if the tableau file is needed I can put something together.