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,

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]

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]

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]

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]

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]

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]