This issue starts with using a functional data model that captures each response to each question by each person taking the survey in a different record of the same table. Then a summary report to provide a count of each response to either a given question or all survey questions (Subtotaled for each question) is quite straight forward. Without that structure, this becomes difficult to impossible to do.
See this link for a discussion of proper Survey Database Design: Need aid on generating a report from a survey layout.
Many thanks for linking to that. I am sure it will help.
This is my first attempt of creating a database so it's just taking a while to get my head around. I have joined Lynda.com to try and get some basic skills/knowledge on all of this.
As a result, I am starting to understand PK and FK and relationships of one to many etc but still it's still a little confusing.
Trying to understand further...
Clearly I have 2 tables that I am looking at -" table 1" is Customer, "table 2" is Survey
Within the survey there will be lets say 20 questions which will either be a choice of set responses or a date for example. The survey will be conducted on many clients but the customer will only have one survey. Does this pass the one to many rule?
From the responses, I may say that I want to pull out all people that answered yes to question 1 and question 4. I understand that each customer will have individual responses but I keep going back to that the question may have many responses but the client will only have 1 response per answer.
I have learnt from the tutorials that it's best to sketch your database and ensure that you have no many to many and one to one relationships... Here is what I have drawn for the 2 tables... Am I right or are you saying infact that I need more tables to produce the information that I will need?
Having read your response on your link... Are you stating my relationships should be as follows to do what I want? If I will only ever have 1 survey then the last box would not be applicable but I think if I have understood correctly - this is probably best being kept in just incase I decide to do further surveys in the future?
Close but not quite
Customers------<Responses>-------Questions>-------Surveys (----< means "one to many")
Customers::__pkCustomerID = Responses::_fkCustomerID
Questions::__pkQuestionID = Responses::_fkQuestionID
Surveys::__pkSurveyID = Questions::_fkSurveyID
For an explanation of the notation that I am using, see the first post of this thread:Common Forum Relationship and Field Notations Explained
You are correct that the Surveys table is not needed if you only maintain one survey in your database. It's purpose is to allow you to set up different groups of questions for different surveys and manage them all in one database. One possible use for such a survey table is that if you, at a later date decide to change the questions used in your survey, you can keep the original survey, questions and responses intact and simply by adding new records, add an updated version of your survey to use with your customers.