Any relationship expert!

[TC]

How about this:

tblStudent
StudentID (primary key)
forname, surname, date of birth, etc.
other details for that student, regardless of activities (if any)

tblActivity
ActivityID (primary key)
activity name (football, basketball, soccer etc.)
other details for that activity, regardless of who does it (if anyone)

tblActivityQuestion
ActivityID ( composite )
QuestionNumber ( primary key )
Text of this question, for that activity
(eg. "How many yards did you run?")

tblActivitySession
StudentID ( composite )
ActivityID ( primary )
Date ( key )
other details for that student on that activity on that date:
excluding activity question answers - see next table.

tblActivityAnswer
StudentID ( composite )
ActivityID ( prim- )
Date ( ary )
QuestionNumber ( key )
student's answer to that question

HTH,
TC

 

[Omar]

Let us say that you wanted to make a database to track PE
activities in school, and require every student in class
to input all physical activities completed each day. You
would like to track 4 major activities; these are
football, basketball, baseball, and soccer. Not only do
you want to know which student in class is doing what
sport (activity) but you would also want the student to
answer specific questions relating to each activity
entered. These questions are only applicable to the
specific activity being inputted. What I mean by this is
that the questions relating to football, are not related
to ,lets say, the other activities. For example:

Football Activity
-question 1: How many yards did you run?
-question 2: Did you play linebacker, QB, Running Back?
-question 3: How many slant patterns did you run?
-etc, etc...

Basketball Activity
-question 1: What position did you play?
-question 2: How good do you dribble?
-Question 3: How many shots did you take?
-etc, etc...

I did this do try to emphasize the need to have different
tables to capture each activity. Hopefully you now
understand the need to have a table for each activity
type. Now lets say that some fields are shared among all 4
activity tables. Fields like hours, student name, grade
level, etc.. Now common sense tells me that I can create a
5th table that captures all the entries that are shared
amongst the activities tables. This 5th table becomes the
mainform, while the other 4 tables become subforms within
it. Now knowing this, how would you setup a relationship
between these 4 activity tables and the 5 table, so you
can accomplish the below?

--Many students be able to insert numerous type of
activities, regardless of type.
--Be able to associate an entry into an activity table
with it's counterpart in the "main table" in table 5. I
would speculate that a many-to-many relationship between
the 4 activity tables and the 5th table would be the way
to go. This would allow many students to input several
different activities and an activity be inputted by many
students. Again, this is just speculation, I do not have a
good grasp on Primary keys and relationships to be
certain.

 

[TC]

Why create a table to list the questions and

another to capture the answer?

The questions are defined once *per activity*.

The answers must be entered once *per student, per activity*.

HTH,
TC

 

[Omar]

TC, please accept my appologies for my lack of knowledge
but I still do not understand what you meant when you
first replied with suggestion. Would it be to much to ask,
for you to explain your login behind your suggestion and
explain what your suggestion is. THanks

 

[TC]

Hi Omar!

The purpose of this table is to define the questions for each activity. When
you ask the questions for an activity, you *get* the questions from *this
table*. You do not "hard code" the questions into your program code. The
first 2 fields are the primary key. Here are some example records from this
table.

ActivityID QuestionNumber QuestionText
FOOTBALL 1 "How many yards did you run?"
FOOTBALL 2 "How many slant patterns did you run?"
BASKETBALL 1 "What position did you play?"
BASKETBALL 2 "How well do you dribble?"


The purpose of this table is to record what activities each student has
done, on what dates. Here are some example records from this table. The
first 3 fields are the primary key. You could also store in this table,
other details for *that student* doing *that activity* on *that date*; for
example, how much the student had to pay for that activity (on that date).

StudentID ActivityID Date Payment
10523 FOOTBALL 1-Jan-2003 $25
10523 BASKETBALL 1-Jan-2003 $20 (basketball is
cheaper)
20345 BASKETBALL 3-Feb-2003 $15 (this guy gets
discounts!)


The purpose of this table is to record the activity question answers. The
first 4 fields are the primary key (or you could do this easier using
autonumbers - but I have not shown that here.) Here are some example records
from this table.

StudentID ActivityID Date QuestionNumber
Answer
10523 FOOTBALL 1-Jan-2003 1 45
10523 BASKETBALL 1-Jan-2003 1
"center"
20345 BASKETBALL 3-Feb-2003 2
"average"

The first example record above, means: student #10523 played football on
1-Jan-2003, and when he was asked football question #1 ("How many yards did
you run?"), he answered 45.

Hope this helps!
TC