A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Design Quandary...



 
 
Thread Tools Display Modes
  #1  
Old October 16th, 2005, 01:03 AM
Access rookie
external usenet poster
 
Posts: n/a
Default Design Quandary...

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.
  #2  
Old October 16th, 2005, 01:16 AM
tina
external usenet poster
 
Posts: n/a
Default Design Quandary...

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 a
tblStudents 1:n tblStudentAnswers
tblQuestions 1:n tblStudentAnswers

hth


"Access rookie" wrote in message
...
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.



  #3  
Old October 16th, 2005, 02:44 AM
Access rookie
external usenet poster
 
Posts: n/a
Default Design Quandary...

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.

"tina" wrote:

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 a
tblStudents 1:n tblStudentAnswers
tblQuestions 1:n tblStudentAnswers

hth


"Access rookie" wrote in message
...
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.




  #4  
Old October 16th, 2005, 04:52 AM
Amy Blankenship
external usenet poster
 
Posts: n/a
Default Design Quandary...

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


"Access rookie" wrote in message
...
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.



  #5  
Old October 16th, 2005, 05:05 AM
tina
external usenet poster
 
Posts: n/a
Default Design Quandary...

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/O...p#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


"Access rookie" wrote in message
...
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.

"tina" wrote:

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 a
tblStudents 1:n tblStudentAnswers
tblQuestions 1:n tblStudentAnswers

hth


"Access rookie" wrote in

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






  #6  
Old October 19th, 2005, 01:32 AM
Access rookie
external usenet poster
 
Posts: n/a
Default Design Quandary...

Thanks Tina,

You rock!

Have a great rest of the week,

Rookie.

"tina" wrote:

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/O...p#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


"Access rookie" wrote in message
...
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.

"tina" wrote:

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 a
tblStudents 1:n tblStudentAnswers
tblQuestions 1:n tblStudentAnswers

hth


"Access rookie" wrote in

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






  #7  
Old October 19th, 2005, 01:32 AM
Access rookie
external usenet poster
 
Posts: n/a
Default Design Quandary...

Thanks for the post Amy,

It's working!

Cheers,

Rookie.

"Amy Blankenship" wrote:

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


"Access rookie" wrote in message
...
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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Best practice for table design Debra Farnham Database Design 20 April 28th, 2005 02:48 PM
Customised design templates 2003 Mary Ann Powerpoint 3 April 25th, 2005 01:18 PM
how do i apply an Axis design or radial design from design templa. nadia Powerpoint 1 April 3rd, 2005 02:21 AM
Design Templates don't apply font sizes consistantly Greg H Powerpoint 1 September 15th, 2004 02:07 PM
opening a document so it is NOT in design mode Brad Pears New Users 1 May 3rd, 2004 09:13 PM


All times are GMT +1. The time now is 01:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.