Summary of Results

[silentcrows]

I've designed a database to hold responses to Surveys. What I want is to allow users to look at the results in a non graphical way, so 15% said Yes 75% said no, etc. I know that I can create queries that count and group by, so those figures are possible, but this means that I have to create a seperate query for each question, and with over 100 questions this gets slightly ridiculous. Secondly, I'm going to hopefully extend the database to encompass all the surveys that have been undertaken to make them easily accessible, so you can see why this would be annoying! Is my only other option to link to a PDF with this information already processed?

Thanks!

[ChipR]

Depending on the structure of the data, you should not have to create a seperate query for each question. What tables do you have?

[ajalwaysus]

you should be able to group by each question on the survey, that way your query will have all questions asked, and all responses as well all in one query.

i.e..

SELECT QUESTION, ANSWER, COUNT(ANSWER) AS COUNTOFANSWER
FROM TABLE_SURVEY
GROUP BY QUESTION, ANSWER
 

You will still need 2 queries, the above query would give you your count by answer (Yes, No), you will then need a query to get you a count of all by question.

i.e..

SELECT QUESTION, COUNT(ANSWER) AS COUNTOFANSWER
FROM TABLE_SURVEY
GROUP BY QUESTION
 

Then you can join both queries to give yourself all the percentages you need.

Let me know if this makes sense,
-AJ

[silentcrows]

It's a table called "Questions" which contains alll the questions with their answers already predefined. Each field is a seperate question. I'm a little perplexed by your answer ajalwaysus, as when you enter your response into a query it then asks to which QUESTION am I referring (i.e. Parameter Value). Am I just being idiotic?

[ajalwaysus]

Please post a table structure, this may help us understand how we need to approach this, my query was meant to be generic, and you would need to tweak it as needed.

-AJ

[silentcrows]

Here's a picture of the structure. I'm sorry if it's stupidly small the upload would only allow, well, stupidly small.

[silentcrows]

This looks better. to me anyway

[ajalwaysus]

OK, this is a lot different than I thought, I don't even know if a query is possible on the table you have now. As a piece of advice, I would rather you do this right, so if you have the time, I think you need to approach this differently. When I designed a survey database, I had at least 4 tables (Questions Table, Answers Table, QuestionXAnswer Table, Results Table).

The Questions table housed all possible questions with this structure:
Question_ID (Auto Number)
Question

The Answers table housed all possible answers with the structure:
Answer_ID (Auto Number)
Answer

The QuestionXAnswer table housed all the cross references of Question and Answer (it gave you the question with each possible answer) with the structure:
Question_ID
Answer_ID

The Results table housed all answers with the structure:
Question
Answer

Now I think this can be made a lot better, survey databases are something I have never been a fan of. I welcome any tweaks or overhauls. =)

[ChipR]

For the most flexibility, I'm thinking something like:

tblQuestions
QuestionID
Question
CorrectAnswer

tblSurvey
SurveyID
Description/Title/Notes

tblSurveyQuestions
SurveyID
QuestionNumber
QuestionID

tblSurveyTaker
TakerID
TakerName
Gender
Age
etc.

tblSurveyTaken
SessionID
SurveyID
TakerID
SurveyDate
Location

tblAnswersGiven
SessionID
QuestionNumber
AnswerGiven

[silentcrows]

I've seen this kind of structure of late (annoyingly after I'd written the database) in a few forums. What I can't quite get my head around is how the answers are stored. So I have a table that contains the question and the possible answers, so a row for each possible answer with repeated question. Then a table of the survey ID and what it is. These then relate into which questions relate to which survey (manual input I'm assuming). I have a table that represents who's responding with their personal data, that then relates into a table of when they took which survey, a session ID. But then, this session ID then relates to a seperate answer given field in another table with just the question number. If I've already had to enter all the responses into the first table, tblQuestions, why then am I entering in the response again. And also does this then mean that for every question response I get there is another field in tbl AnswersGiven?

Sorry just trying to wrap my head around it all!

[ChipR]

For multiple choice, my tblQuestions would have to have the choices added. Maybe:

tblQuestions
QuestionID
Question
ChoiceA
ChoiceB
ChoiceC
ChoiceD
CorrectAnswer

Notice there is no field for a response. The question is its own entity.

Yes, in my design, every response is a record in tblAnswersGiven.

[silentcrows]

Hi,

So, if there are questions with different numbers of choices then it would just be case of leaving those fields blank yes? I'm not sure I understand what the CorrectAnswer field is for, being a survey there is no correct answer.

Sorry, feel like im probably being dense!

Alex

[ChipR]

As you can see, I'm no expert on surveys. You don't need a CorrectAnswer field if there isn't one. I think would arrange the choices in records that way, but if you want a more variable number of choices, you could make another table of

tblChoices
QuestionID
Choice

[silentcrows]

So, I think I'm nearly there, the last thing I think I need help with is the answer given field in tblAnswersGiven, I understand how to get it available so that they can enter a response but how do I limit it to the choices available? I can't use the validation text property or the input mask obviously, does this mean that for each individual input box on a form I would have to format them?

[ChipR]

Seems like a combo box would work. The source would just be
"SELECT Choice FROM tblChoices WHERE QuestionID = " & QuestionID
or the list of values of ChoiceA, ChoiceB, ChoiceC, ChoiceD. Depending on how you structured it.

[NeoPa]

I don't want to take anyone off track here (all going well it seems) but I suspect (from your original design) that you might benefit greatly from going through Normalisation and Table structures, provided by one of our most prolific contributors.

[silentcrows]

I do understand normalization, I promise!

Got a slight other problem, all relationships set up and works in that sense. Just producing forms and I can create a form that allows me to create questions and assign choices per question, however I need a form that will relate those questions to the specific survey. What I was thinking of is a form with a subform, so the main form is selecting which survey and then the subform contains records of the question and question number, but this means to relate them I need to have the SurveyID in the subform (obviously) but of course the form is trying to generate new records where I actually need it to just look up whats already been input in a way that a layman would understand. Is it sensible to just use the table tblSurveyQuestions and turn it directly into a form and then mask the SurveyID and the QuestionID? This would be annoying however as for every new assignment you'd have to select the Survey again!

Thoughts?

[silentcrows]

Oh thought it might be helpful to post my relationships