queries between multiple tables

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

purpleflash

Guest

n/a Posts

July 4th, 2008
12:15 AM
#2

Re: queries between multiple tables

On 3 Jul, 20:49, alex.william...@gmail.com wrote:

Quote:

Originally Posted by

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

alex.williams56@gmail.com

Guest

n/a Posts

July 4th, 2008
08:05 PM
#3

Re: queries between multiple tables

On Jul 3, 8:15*pm, purpleflash <k...@bgs.ac.ukwrote:

Quote:

Originally Posted by

On 3 Jul, 20:49, alex.william...@gmail.com wrote:
>
>
>

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

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)

Quote:

Originally Posted by

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)

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

So I have two questions at this point:

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

I want to plug in a specific question's ID number and have access pull
up a table that looks something like this:

Quote:

Originally Posted by

Survey * * / * *P value * / * R value ....

Quote:

Originally Posted by

(date) * * * * (double) * * * * (double)
1/1/02 * * * * .82 * * * * * * * * .31
6/1/02 * * * * .59 * * * * * * * * .29
1/1/03 * * * * .73 * * * * * * * * .25
...

Quote:

Originally Posted by

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).

Quote:

Originally Posted by

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!