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
|
|||
|
|||
Questions with Requirements
I have 1-table with 3 fields. (AutoNbr), Number (Nbr), questions(memo
field). For each record there will be questions. I have another table with requirements with several fields (AutoNbr), ClassID (Nbr), Class Name (txt), etc... My plan is to design a form and use a dropdown menu to select questions. After the question is selected I would like to display list of coursed required. The second part is that some classes will need to be displayed on one or more questions. Thank you, |
#2
|
|||
|
|||
Questions with Requirements
On Fri, 5 Jun 2009 13:49:01 -0700, NFL wrote:
I have 1-table with 3 fields. (AutoNbr), Number (Nbr), questions(memo field). For each record there will be questions. I have another table with requirements with several fields (AutoNbr), ClassID (Nbr), Class Name (txt), etc... My plan is to design a form and use a dropdown menu to select questions. After the question is selected I would like to display list of coursed required. The second part is that some classes will need to be displayed on one or more questions. Thank you, You need another table with links to the class and questions table. A couple of questions: If the Nbr field in the questions table uniquely identifies the question, do you really need a separate autonumber? Microsoft may give you the impression that every table needs an autonumber primary key, but that is not the case; the Primary Key must be unique, and should be short (e.g. a number field or a small text field) and stable, but it need not be an autonumber. The same applies to your class table. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Questions with Requirements
Hello:
The ClassID that identifies all courses/requirements is stable. The questions table is where I'm stuck and not sure how to make it link to the course table. This is something I want to add to the main dbase which already has links to courses, students, and instructors. I was playing around with the idea to add a question table that will link to the course table to help supervisors decide what training needs to be scheduled. For example: 1st question "Will this person be working with customer relations?", If yes, then "course1", "course 2", will appear on the form. 2nd question "Will this person work on... blah ... blah", If yes then "course 1", "course 4", etc., needs to be scheduled. Could I create a separate form using the question table and courses table? I was thinking of using list or dropdown menu to pick a question and depending on what question is selected a list of courses will appear on a bound or unbound field? The courses will be brief using 3 fields (ClassID [primary-nbr]) (ClassName [text]) & (ClassTitle [text]). Thank you for your help! "John W. Vinson" wrote: On Fri, 5 Jun 2009 13:49:01 -0700, NFL wrote: I have 1-table with 3 fields. (AutoNbr), Number (Nbr), questions(memo field). For each record there will be questions. I have another table with requirements with several fields (AutoNbr), ClassID (Nbr), Class Name (txt), etc... My plan is to design a form and use a dropdown menu to select questions. After the question is selected I would like to display list of coursed required. The second part is that some classes will need to be displayed on one or more questions. Thank you, You need another table with links to the class and questions table. A couple of questions: If the Nbr field in the questions table uniquely identifies the question, do you really need a separate autonumber? Microsoft may give you the impression that every table needs an autonumber primary key, but that is not the case; the Primary Key must be unique, and should be short (e.g. a number field or a small text field) and stable, but it need not be an autonumber. The same applies to your class table. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Questions with Requirements
On Sat, 6 Jun 2009 07:03:05 -0700, NFL wrote:
Hello: The ClassID that identifies all courses/requirements is stable. The questions table is where I'm stuck and not sure how to make it link to the course table. If each Course can reference several questions, and each Question can be used in may courses, then you need ANOTHER TABLE. See below. This is something I want to add to the main dbase which already has links to courses, students, and instructors. I was playing around with the idea to add a question table that will link to the course table to help supervisors decide what training needs to be scheduled. For example: 1st question "Will this person be working with customer relations?", If yes, then "course1", "course 2", will appear on the form. 2nd question "Will this person work on... blah ... blah", If yes then "course 1", "course 4", etc., needs to be scheduled. Could I create a separate form using the question table and courses table? I was thinking of using list or dropdown menu to pick a question and depending on what question is selected a list of courses will appear on a bound or unbound field? The courses will be brief using 3 fields (ClassID [primary-nbr]) (ClassName [text]) & (ClassTitle [text]). STOP. You're jumping into forms too early! Get your table structure right *first*. You will (at least) need a new table relating courses to questions, with a CourseID and a QuestionNo, and some other fields indicating the nature of the relationship. You'll probably also need to account for relationships between courses and other courses (e.g. prerequisites). -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Questions with Requirements
That's where I'm stuck. How would I create a relationship (1 to many) if not
every question is related to all? I apologize for jumping ahead to quick.. "John W. Vinson" wrote: On Sat, 6 Jun 2009 07:03:05 -0700, NFL wrote: Hello: The ClassID that identifies all courses/requirements is stable. The questions table is where I'm stuck and not sure how to make it link to the course table. If each Course can reference several questions, and each Question can be used in may courses, then you need ANOTHER TABLE. See below. This is something I want to add to the main dbase which already has links to courses, students, and instructors. I was playing around with the idea to add a question table that will link to the course table to help supervisors decide what training needs to be scheduled. For example: 1st question "Will this person be working with customer relations?", If yes, then "course1", "course 2", will appear on the form. 2nd question "Will this person work on... blah ... blah", If yes then "course 1", "course 4", etc., needs to be scheduled. Could I create a separate form using the question table and courses table? I was thinking of using list or dropdown menu to pick a question and depending on what question is selected a list of courses will appear on a bound or unbound field? The courses will be brief using 3 fields (ClassID [primary-nbr]) (ClassName [text]) & (ClassTitle [text]). STOP. You're jumping into forms too early! Get your table structure right *first*. You will (at least) need a new table relating courses to questions, with a CourseID and a QuestionNo, and some other fields indicating the nature of the relationship. You'll probably also need to account for relationships between courses and other courses (e.g. prerequisites). -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Questions with Requirements
On Mon, 8 Jun 2009 06:45:01 -0700, NFL wrote:
That's where I'm stuck. How would I create a relationship (1 to many) if not every question is related to all? I apologize for jumping ahead to quick.. It's a classic, basic principle in relational design: a many to many relationship is decomposed into two one to many relationships. The Northwind sample database has a many to many relationhship from Orders to Products; this is implemented with the OrderDetails table. Each Order has many OrderDetails; each Product is in many OrderDetails. In your case you would have three tables: Classes, Questions, and ClassQuestions. The latter would be on the many side of a relationship to each of the first two tables, which would not be related to one another. E.g. you might have Classes ClassID: 1; ClassName: Database Design 101 ClassID: 2; ClassName: Principles of Programming ClassID: 3; ClassName: Excel Spreadsheet Design Questions: Q: 1: What is the first step of a project? A: Identify the problem to be solved. Q: 2: State the first three normal forms. A: The key, the whole key, and nothing but the key, so help me Codd. ClassQuestions: ClassID: 1; Q: 1 this question is relevant to this class ClassID: 2; Q; 1 it's relevant to this class too ClassID: 3; Q: 1 and to this ClassID: 1; Q: 2 Q2 is only applicable to database design (let's say) -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Questions with Requirements
Thank you for your quick response. That's what I was afraid of. Your input
really helped! Thank again! "John W. Vinson" wrote: On Mon, 8 Jun 2009 06:45:01 -0700, NFL wrote: That's where I'm stuck. How would I create a relationship (1 to many) if not every question is related to all? I apologize for jumping ahead to quick.. It's a classic, basic principle in relational design: a many to many relationship is decomposed into two one to many relationships. The Northwind sample database has a many to many relationhship from Orders to Products; this is implemented with the OrderDetails table. Each Order has many OrderDetails; each Product is in many OrderDetails. In your case you would have three tables: Classes, Questions, and ClassQuestions. The latter would be on the many side of a relationship to each of the first two tables, which would not be related to one another. E.g. you might have Classes ClassID: 1; ClassName: Database Design 101 ClassID: 2; ClassName: Principles of Programming ClassID: 3; ClassName: Excel Spreadsheet Design Questions: Q: 1: What is the first step of a project? A: Identify the problem to be solved. Q: 2: State the first three normal forms. A: The key, the whole key, and nothing but the key, so help me Codd. ClassQuestions: ClassID: 1; Q: 1 this question is relevant to this class ClassID: 2; Q; 1 it's relevant to this class too ClassID: 3; Q: 1 and to this ClassID: 1; Q: 2 Q2 is only applicable to database design (let's say) -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|