# Bet 5 paired survey answers out of 15 **URL:** \ **Category:** Factual Questions\ **Created:** [January 26, 2016, 6:40am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240 "2016-01-26T06:40:42Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Stone.Jane](https://avatars.discourse-cdn.com/v4/letter/s/ed8c4c/32.png) [@Stone.Jane](https://boards.straightdope.com/u/Stone.Jane)\ **Post date:** [January 26, 2016, 6:40am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/1 "2016-01-26T06:40:42Z")
This question has probably been asked and answered already, if so please tell me how to search the site or post a link to the answer. I’m trying to figure out how to query a MS Access table with 20 possible answers for the top 5 paired answers. For example: the table consists of a primary key and 21 coded responses. For example “I would participate more if”: 1 = Time fit with my schedule, 2 = Transportation wasn’t an issue, 3 = Issue was more interesting to me, etc. There are 20 fields with the answers populated and field # 21 is left for a user defined comment. There is no need to consider field 21 as relevant to the most frequent answers. I need to get the top 5 best paired answers. I didn’t set this survey up and I offered to help when asked, but I really have no idea where to start. I put the raw data in Access because I use it, but not usually for something like this. I could also put the data in Excel if that would be a better choice. Any help would be greatly appreciated. ---
**Author:** ![engineer\_comp\_geek](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/engineer_comp_geek/32/504_2.png) [@engineer\_comp\_geek](https://boards.straightdope.com/u/engineer_comp_geek)\ **Post date:** [January 26, 2016, 8:06am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/2 "2016-01-26T08:06:19Z")
**Moderator Action** Welcome to the SDMB, **Stone.Jane**. You posed this in our About This Message Board forum, which is for questions about the message board itself, like problems or issues posting, questions about the rules here, etc. Since your question has a factual answer, it belongs in our General Questions forum. I will move the thread for you. Please take the time to read the forum descriptions so that you place your posts in the appropriate forum. Moving thread from About This Message Board to General Questions. ---
**Author:** ![md2000](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@md2000](https://boards.straightdope.com/u/md2000)\ **Post date:** [January 26, 2016, 3:49pm UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/3 "2016-01-26T15:49:48Z")
I assume you have data in to form of: RespondentID Answer1 answer2 answer3 etc. If I understand it, you are looking for - if someone says “5” as the answer for Q1 and Q12 or Q3 and Q19 that’s a “paired answer”. You are looking for the most frequent such pairs. Does it matter that one respondent paired Q3 and Q8 with answer #5, and another paired the same questions with response #3? Or are you in fact looking for common triples, i.e. “Q3,Q8,#5” Since “who answered” is not important, I would: Run a program to generate a table of the triples from the dataset - not real Access code… For A= Q1 to Q20 For B= A+1 to Q21 If response(A) = Response(B) then Write record Respondent#, A, B, Response(A) /\* Use respondent # to guarantee distinct data rows End If End For End for -now, you want to sort by Q1, Q2, response and generate summaries in another table- QA, QB, Response#, count (If you don’t care which of the 5 responses, then summarize only by QA, QB, number of responses At that point - sort by count of pairs. ---
**Author:** ![Stone.Jane](https://avatars.discourse-cdn.com/v4/letter/s/ed8c4c/32.png) [@Stone.Jane](https://boards.straightdope.com/u/Stone.Jane)\ **Post date:** [January 27, 2016, 5:47am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/4 "2016-01-27T05:47:46Z")
Thank you for putting it where I should have. ---
**Author:** ![Stone.Jane](https://avatars.discourse-cdn.com/v4/letter/s/ed8c4c/32.png) [@Stone.Jane](https://boards.straightdope.com/u/Stone.Jane)\ **Post date:** [January 27, 2016, 6:00am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/5 "2016-01-27T06:00:15Z")
Thank you, I’ll give this a try and see if that will meet their needs. I’ll let you know. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [January 27, 2016, 11:46pm UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/6 "2016-01-27T23:46:53Z")
I am still having hard time understanding the OP, so if **md2000** ’s program didn’t already fix it, here is another interpretation of the problem in a spreadsheet: > **[Paired Survey Answers?](https://docs.google.com/spreadsheets/d/16B49qyueLSHq_R3hdBqtLKXC-x4x4RgoTCzfE2zcwec/edit?usp=sharing)** > > Sheet1 > > Choice 1,Choice 2,Choice 3,Choice 4,Choice 5 > Most Frequent Answer:,A,B,C,L,C > \# of ppl with that answer:,28,37,16,15,16 > Respondent 1,A,B,C,U,S > Respondent 2,A,B,D,S,K > Respondent 3,A,B,R,T,C > Respondent 4,A,B,C,B,O > Respondent... This highlights the most popular question-answer pairs from 200 people, 15 questions each, 20 possible answers per question. For example: - 20 people answered Question 2 with answer “15 - I don’t understand this issue.” - 19 people answered Question 8 with answer “4 - Issue was more interesting to me.” - Another 19 people answered Question 12 with answer “8 - Blah blah blah.” That would get you the top 3 question-answer pairs. In the actual spreadsheet there are some ties, so it’d really be the Top 7 rather than the Top 5, but is that what you’re looking for? If so: - It should be possible to get that data via a SQL query in Access too. Or just export it to Excel. - With a bigger dataset (more people who answered questions) and a less randomized distribution of answers, ties for the Top X pairs are less likely to be a problem. Otherwise, if neither md2000’s nor this answer solves your problem, could you please rephrase the OP in terms of what kind of answer you want? “I want to know the most popular answers per question” or “most popular question-answer pairs across all questions” or “show me the top 5 strongest correlations in the results, e.g. people who answer Q7 with answer 14 also tend to answer Q8 with answer 12” ---
**Author:** ![Stone.Jane](https://avatars.discourse-cdn.com/v4/letter/s/ed8c4c/32.png) [@Stone.Jane](https://boards.straightdope.com/u/Stone.Jane)\ **Post date:** [January 28, 2016, 7:11am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/7 "2016-01-28T07:11:02Z")
I totally messed this up and this is a great frustration for me at my work. Let me start over and make this much simpler and much more clear. I have a MS Access table with the following fields: Primary Key, RespondentID, Choice1, Choice2, Choice 3, etc. So, RespondentID 1 has chosen code A for Choice1, B for Choice2, C for Choice3, D for Choice4 and E for Choice5. RespondentID 2 has chosen B for Choice1, C for Choice2, F for Choice3, H for Choice4 and M for Choice5. I have just over 1,000 Respondents and I want to find the most popular Choice pairs. I want to find the most common 5 answers that all respondents agreed to and the most most common 4 answers that all respondents agreed to, and the most common 3 answers that all respondents agreed to. I’ve got like 20 possible responses for each question and only 5 answers that are recorded. I hope this is much clearer than my original post and I appreciate your patience with my post. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [January 28, 2016, 8:24am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/8 "2016-01-28T08:24:17Z")
> [@Stone.Jane](#): > > I totally messed this up and this is a great frustration for me at my work. Let me start over and make this much simpler and much more clear. I have a MS Access table with the following fields: Primary Key, RespondentID, Choice1, Choice2, Choice 3, etc. So, RespondentID 1 has chosen code A for Choice1, B for Choice2, C for Choice3, D for Choice4 and E for Choice5. RespondentID 2 has chosen B for Choice1, C for Choice2, F for Choice3, H for Choice4 and M for Choice5. I have just over 1,000 Respondents and I want to find the most popular Choice pairs. I want to find the most common 5 answers that all respondents agreed to and the most most common 4 answers that all respondents agreed to, and the most common 3 answers that all respondents agreed to. I’ve got like 20 possible responses for each question and only 5 answers that are recorded. I hope this is much clearer than my original post and I appreciate your patience with my post. So you mean you want to know what the most common answer to Choice1 was, what the most common answer to Choice2 was, etc.? And the choices have nothing to do with each other? ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [January 28, 2016, 8:37am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/9 "2016-01-28T08:37:19Z")
> [@Reply](#): > > So you mean you want to know what the most common answer to Choice1 was, what the most common answer to Choice2 was, etc.? And the choices have nothing to do with each other? If so, I updated the previous Google Sheet to work with letters as answers instead of numbers: > **[Paired Survey Answers?](https://docs.google.com/spreadsheets/d/16B49qyueLSHq_R3hdBqtLKXC-x4x4RgoTCzfE2zcwec/edit#gid=0)** > > This Sheet is private Based on this post, which also provides instructions for Excel: > ---
**Author:** ![Stone.Jane](https://avatars.discourse-cdn.com/v4/letter/s/ed8c4c/32.png) [@Stone.Jane](https://boards.straightdope.com/u/Stone.Jane)\ **Post date:** [January 29, 2016, 6:28am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/10 "2016-01-29T06:28:45Z")
No, I do care what the choices have to do with each other. I’m looking for the best “paired” choices". If 28 people picked A and those 28 people also picked B what was their third best choice? What 3 pair combination of answers were the highest ranked? What 4 pair combination of answers were the highest ranked? They were allowed to rank many choices, I need to find the highest ranked set of 3, 4, 5 or 6 answers. I hope that clarifies what this poll is and why I’m so lost on figuring this out. Thank you for your patience with this. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [January 30, 2016, 7:21am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/11 "2016-01-30T07:21:32Z")
Do you mean “If a respondent answered A for question 1 and B for question 2, what was their most likely answer for question 3?”? ---
**Author:** ![Stone.Jane](https://avatars.discourse-cdn.com/v4/letter/s/ed8c4c/32.png) [@Stone.Jane](https://boards.straightdope.com/u/Stone.Jane)\ **Post date:** [January 31, 2016, 7:14am UTC](https://boards.straightdope.com/t/bet-5-paired-survey-answers-out-of-15/744240/12 "2016-01-31T07:14:20Z")
No, I don’t mean that. I know the answers I just need to summarize it. What I have is survey data. Respondents ranked their answers to each question by putting what was most important to them by putting their first choice in Choice\_1, their second choice in Choice\_2, third choice in Choice\_3, etc. I am looking to find out what the most popular paired answers were to each question. For example: Respondent\_id Choice\_1 Choice\_2 Choice\_3 Choice\_4 Choice\_5 Choice\_6 01 A C D J K L 02 A D J L M O 03 B C D J K N 04 C D J K O P Of almost 1,000 respondents, which combinations were the most popular. Looking at the data above it looks like the combination of C, D, J, K were the best choices as they appear in the top 6 choices for 3 of the respondents. How do I figure out the most popular 5 choice combinations, the most popular 4 choice combinations, the most popular 3 choice combinations? Some questions were ranked by up to 15 choices, so there is a lot of data to look at. Again, any help would be appreciated.