On 3 Jul, 20:49, alex.william...@[EMAIL PROTECTED]
wrote:
> I'm relatively new when it comes to access but I think I have a grasp
> on the basics. I have a very specific problem that requires a little
> help from someone more experienced.
>
> I'm trying to help someone in the field of consulting create a more
> comprehensive database for their data they collected from surveys.
> Here's the background:
>
> Three surveys are sent a year, each with similar questions, but the
> surveys are continually revised (new questions are added, and old
> questions that haven't been helpful are taken out)
>
> I have a table that holds all the questions (an ID #, the question,
> the 5 multiple choice answers, the number (1-5) of the correct answer,
> and a 'section' column that marks the topic the question aims at
> asking)
>
> My friend would also like to store the test statistics that he
> generates for each survey he hands out. So I'm planning to set up one
> table for every survey he sent out (which amounts to about 20 tables
> so far). The leftmost column holds the question's ID number, and the
> other columns hold several significant statistics - the percentage of
> respondents who got the question right, e.g. Note: I don't understand
> all the statistics behind this, I'm just trying to set up the database
> so it will be easier for him while he expands it.
>
> So I have two questions at this point:
>
> 1) is there a more efficient way to store the data of each survey,
> rather than just making a new table? The reason I did that is because
> each survey is slightly different, and i can't conceive of making one
> table that can efficiently store all of it. However, if anybody has a
> better way, I would be willing to hear it.
>
> 2) when i run a query, I want to be able to track each question's
> statistics over time. So, when i run the query i use the criteria row
> to zero in on one question (by typing in the question's ID). Now my
> problem is that since I have 20 tables for each survey, there is no
> easy way I know of doing this. What I want is this:
>
> I want to plug in a specific question's ID number and have access pull
> up a table that looks something like this:
>
> Survey =A0 =A0 / =A0 =A0P value =A0 / =A0 R value ....
>
> (date) =A0 =A0 =A0 =A0 (double) =A0 =A0 =A0 =A0 (double)
> 1/1/02 =A0 =A0 =A0 =A0 .82 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .31
> 6/1/02 =A0 =A0 =A0 =A0 .59 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .29
> 1/1/03 =A0 =A0 =A0 =A0 .73 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .25
> ...
>
> Sorry if this is all confusing. The basis of the problem is that there
> are 3 variables (question ID #, date of the survey, and the statistics
> of the question) while there is only a 2 variables that can be
> represented in a table (to my knowledge).
>
> Thanks
For the first part try three tables!
table 1 =3D tblSurvey (surveyID(PrimaryKey), SurveyDate, Survey_details
etc)
table 2 =3D tblQuestions (QuestionID(PrimaryKey), SurveyID(ForeignKey),
Question, AnswerGiven, AnswerExpected etc)
table 3 =3D tblStats (StatID(PK), QuestionID(FK), StatisticType,
StatisticValue) - each row has ony 1 statistic in it
This way you can store all questions associated with Surveys
efficiently
|
