Design Quandary...

[Access rookie]

Hello,

I'm trying to put together a database that hosts the answers to questions
posed to students. I have all the students in one table, all the questions in
another, and I'm attempting to put all the answers in one table.

Here's my thoughts: Student to Questions: Many to many linked by a junction
table.
Question to answer: One to many.

However, I'm confused; each question only has one answer, but then again,
many students answer the same question, with different answers.

Any help would be greatly appreciated.

Thanks,

Rookie.

 

[tina]

However, I'm confused; each question only has one answer, but then again,

many students answer the same question, with different answers.

do you mean that each question has only one *correct* answer? if you're
simply tracking each answer that each student gives to each question, then
"correctness" is not really an issue, is it? for simple tracking, you're on
the right "track" (pun intended <g>). three data tables should do it:

tblStudents
StudentID (primary key)
[other fields that describe a student, such as FirstName, LastName, etc.]

tblQuestions
QuestionID (pk)
Question
[if each question has only one correct answer, you could include a field for
CorrectAnswer in this table, if you need it.]

tblStudentAnswers
StudentID (foreign key from tblStudents)
QuestionID (foreign key from tblQuestions)
Answer
[if each student may answer each question *only once*, then you can use the
two foreign key fields as a combination primary key for this table.
otherwise, you can add an AnswerID field as the primary key field.]

the relationships are:
tblStudents 1:n tblStudentAnswers
tblQuestions 1:n tblStudentAnswers

hth

 

[Access rookie]

Hey Tina,

Thanks for your reply. I've made the necessary changes. No, there's no
correct answer to each question. It was a survey.

I do want to find out how to build a form based on your recommendations. I
have a query using the junction table and the student table, but I can't see
the question on the form (duh, the question field is not in either table.)
When I try and add the question table, it tells me it can't be performed due
to ambiguous outer joins.

Any bright ideas?

Thanks,

Rookie.

However, I'm confused; each question only has one answer, but then again,
many students answer the same question, with different answers.

do you mean that each question has only one *correct* answer? if you're
simply tracking each answer that each student gives to each question, then
"correctness" is not really an issue, is it? for simple tracking, you're on
the right "track" (pun intended <g>). three data tables should do it:

tblStudents
StudentID (primary key)
[other fields that describe a student, such as FirstName, LastName, etc.]

tblQuestions
QuestionID (pk)
Question
[if each question has only one correct answer, you could include a field for
CorrectAnswer in this table, if you need it.]

tblStudentAnswers
StudentID (foreign key from tblStudents)
QuestionID (foreign key from tblQuestions)
Answer
[if each student may answer each question *only once*, then you can use the
two foreign key fields as a combination primary key for this table.
otherwise, you can add an AnswerID field as the primary key field.]

the relationships are:
tblStudents 1:n tblStudentAnswers
tblQuestions 1:n tblStudentAnswers

hth

Hello,

I'm trying to put together a database that hosts the answers to questions
posed to students. I have all the students in one table, all the questions in
another, and I'm attempting to put all the answers in one table.

Here's my thoughts: Student to Questions: Many to many linked by a junction
table.
Question to answer: One to many.

However, I'm confused; each question only has one answer, but then again,
many students answer the same question, with different answers.

Any help would be greatly appreciated.

Thanks,

Rookie.

 

[Amy Blankenship]

What you need is this:

Students
=======
StudentID (PK)
FirstName
LastName
etc

Questions
=======
QuestionID (Autonumer PK)
StemText
QuestionOrder
QuestionnaireID (would point to a questionnaire table that would allow you
to have multiple questionnaires in one DB)

Answer
========
AnswerID (Autonumer PK)
QuestionID
AnswerText ' this is the actual text the student would see in the a, b, c,
d, etc choice
IsCorrect (yes/no)

If you want to allow for users to go back and answer questions multiple
times, you'll need

Sessions
======
SessionID (PK Autonumber)
StudentID
QuestionnaireID (would point to a questionnaire table that would allow you
to have multiple questionnaires in one DB)

StudentResponses
============
SessionID
AnswerID

If you don't want to allow the user to go back and take the quiz multiple
times (you'd delete and recreate the record each time the question was
presented):

StudentResponses
============
StudentID
AnswerID

HTH;

Amy

 

[tina]

well, the standard interface for two tables (parents) linked by a third
table (child) is: main form bound to one parent table; subform bound to
child table; other parent table used as the RowSource of a combo box control
in the subform. exactly how you set up the forms depends on how the data
entry will be done - what you're after at this point is efficient, easy data
entry for the end-user.

since this is a survey, though - before you spend a lot of time re-inventing
the wheel, suggest you take a look at Access MVP Duane Hookom's AtYourSurvey
database.
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
the database is a free download; it's frequently recommended in these
newsgroups; and it's been out for awhile, so it's been thoroughly
"road-tested". you may find that you can use it as is, or tweak it to fit
your specific needs, or just study the design to learn, get new ideas, etc.

hth

Hey Tina,

Thanks for your reply. I've made the necessary changes. No, there's no
correct answer to each question. It was a survey.

I do want to find out how to build a form based on your recommendations. I
have a query using the junction table and the student table, but I can't see
the question on the form (duh, the question field is not in either table.)
When I try and add the question table, it tells me it can't be performed due
to ambiguous outer joins.

Any bright ideas?

Thanks,

Rookie.

However, I'm confused; each question only has one answer, but then again,
many students answer the same question, with different answers.

do you mean that each question has only one *correct* answer? if you're
simply tracking each answer that each student gives to each question, then
"correctness" is not really an issue, is it? for simple tracking, you're on
the right "track" (pun intended <g>). three data tables should do it:

tblStudents
StudentID (primary key)
[other fields that describe a student, such as FirstName, LastName, etc.]

tblQuestions
QuestionID (pk)
Question
[if each question has only one correct answer, you could include a field for
CorrectAnswer in this table, if you need it.]

tblStudentAnswers
StudentID (foreign key from tblStudents)
QuestionID (foreign key from tblQuestions)
Answer
[if each student may answer each question *only once*, then you can use the
two foreign key fields as a combination primary key for this table.
otherwise, you can add an AnswerID field as the primary key field.]

the relationships are:
tblStudents 1:n tblStudentAnswers
tblQuestions 1:n tblStudentAnswers

hth

Hello,

I'm trying to put together a database that hosts the answers to questions
posed to students. I have all the students in one table, all the

questions
in

another, and I'm attempting to put all the answers in one table.

Here's my thoughts: Student to Questions: Many to many linked by a junction
table.
Question to answer: One to many.

However, I'm confused; each question only has one answer, but then again,
many students answer the same question, with different answers.

Any help would be greatly appreciated.

Thanks,

Rookie.

 

[Access rookie]

Thanks Tina,

You rock!

Have a great rest of the week,

Rookie.

well, the standard interface for two tables (parents) linked by a third
table (child) is: main form bound to one parent table; subform bound to
child table; other parent table used as the RowSource of a combo box control
in the subform. exactly how you set up the forms depends on how the data
entry will be done - what you're after at this point is efficient, easy data
entry for the end-user.

since this is a survey, though - before you spend a lot of time re-inventing
the wheel, suggest you take a look at Access MVP Duane Hookom's AtYourSurvey
database.
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
the database is a free download; it's frequently recommended in these
newsgroups; and it's been out for awhile, so it's been thoroughly
"road-tested". you may find that you can use it as is, or tweak it to fit
your specific needs, or just study the design to learn, get new ideas, etc.

hth

Hey Tina,

Thanks for your reply. I've made the necessary changes. No, there's no
correct answer to each question. It was a survey.

I do want to find out how to build a form based on your recommendations. I
have a query using the junction table and the student table, but I can't see
the question on the form (duh, the question field is not in either table.)
When I try and add the question table, it tells me it can't be performed due
to ambiguous outer joins.

Any bright ideas?

Thanks,

Rookie.

However, I'm confused; each question only has one answer, but then again,
many students answer the same question, with different answers.

do you mean that each question has only one *correct* answer? if you're
simply tracking each answer that each student gives to each question, then
"correctness" is not really an issue, is it? for simple tracking, you're on
the right "track" (pun intended <g>). three data tables should do it:

tblStudents
StudentID (primary key)
[other fields that describe a student, such as FirstName, LastName, etc.]

tblQuestions
QuestionID (pk)
Question
[if each question has only one correct answer, you could include a field for
CorrectAnswer in this table, if you need it.]

tblStudentAnswers
StudentID (foreign key from tblStudents)
QuestionID (foreign key from tblQuestions)
Answer
[if each student may answer each question *only once*, then you can use the
two foreign key fields as a combination primary key for this table.
otherwise, you can add an AnswerID field as the primary key field.]

the relationships are:
tblStudents 1:n tblStudentAnswers
tblQuestions 1:n tblStudentAnswers

hth


Hello,

I'm trying to put together a database that hosts the answers to questions
posed to students. I have all the students in one table, all the questions
in
another, and I'm attempting to put all the answers in one table.

Here's my thoughts: Student to Questions: Many to many linked by a
junction
table.
Question to answer: One to many.

However, I'm confused; each question only has one answer, but then again,
many students answer the same question, with different answers.

Any help would be greatly appreciated.

Thanks,

Rookie.

 

[Access rookie]

Thanks for the post Amy,

It's working!

Cheers,

Rookie.