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.
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.
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.
Thank you for putting it where I should have.
Thank you, I’ll give this a try and see if that will meet their needs. I’ll let you know.
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:
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”
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?
If so, I updated the previous Google Sheet to work with letters as answers instead of numbers:
Based on this post, which also provides instructions for Excel:
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.
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?”?
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.