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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |