On Jul 3, 8:15=A0pm, purpleflash <k...@[EMAIL PROTECTED]
> wrote:
> 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
Thank you thats a very good way to do it!
|
