Prev: Form Data Source
Next: VBA ODBC Append Query
From: alex.williams56 on 3 Jul 2008 15:49
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 / P value / R value ....

(date) (double) (double)
1/1/02 .82 .31
6/1/02 .59 .29
1/1/03 .73 .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
From: purpleflash on 3 Jul 2008 20:15
On 3 Jul, 20:49, alex.william...(a)gmail.com 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     /    P value   /   R value ....
>
> (date)         (double)         (double)
> 1/1/02         .82                 .31
> 6/1/02         .59                 .29
> 1/1/03         .73                 .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 = tblSurvey (surveyID(PrimaryKey), SurveyDate, Survey_details
etc)

table 2 = tblQuestions (QuestionID(PrimaryKey), SurveyID(ForeignKey),
Question, AnswerGiven, AnswerExpected etc)

table 3 = 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
From: alex.williams56 on 4 Jul 2008 15:55
On Jul 3, 8:15 pm, purpleflash <k...(a)bgs.ac.uk> wrote:
> On 3 Jul, 20:49, alex.william...(a)gmail.com 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     /    P value   /   R value ....
>
> > (date)         (double)         (double)
> > 1/1/02         .82                 .31
> > 6/1/02         .59                 .29
> > 1/1/03         .73                 .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 = tblSurvey (surveyID(PrimaryKey), SurveyDate, Survey_details
> etc)
>
> table 2 = tblQuestions (QuestionID(PrimaryKey), SurveyID(ForeignKey),
> Question, AnswerGiven, AnswerExpected etc)
>
> table 3 = 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!
 | 
Pages: 1
Prev: Form Data Source
Next: VBA ODBC Append Query