Tables and Forms

[Guest]

This question is related to an existing school database:

In this database I want to search and or enter the students thru the form.
I would like to avoid typing the students info again if he or she wants to
enroll into another class.

I do understand that I need to use the relational database features.
Therefore, I created a:
Table for student (with personal info)
Table for classes (related to the classes, ex: grades, class code, class
startdate ..)
In the Student table I'm using SSN as the primary key.
In the classes table I have also included the field SSN (but not as a
primary key)
I guess, this is where I get stuck. I'm going crazy solving this. Can
someone please help me. Thank you so much.

 

[Douglas J. Steele]

Since one student can take many classes, and since each class can have many
students take it, you have what's known as a many-to-many relationship
between the Student and Class tables. To resolve a many-to-many
relationship, you need to introduce a 3rd table, normally referred to as an
intersection table. For the sake of argument, call it StudentClass. The
primary key of StudentClass will be SSN from Student combined with whatever
the primary key of Class is. In fact, depending on how your Class table is
structured (do you have 1 entry per course, or 1 entry per course
offering?), you might need to add additional fields to the primary key of
StudentClass to handle the case of one student taking the same course
several times.

By the way, you might want to reconsider using SSN as a primary key. My
understanding is that the US SSNs are not actually guaranteed to be unique.
As well, what happens if you have a student who doesn't have an SSN?

 

[Guest]

Thank you for replying. I'm still confused. You wanted to know if (do I
have 1 entry per course, or 1 entry per course offering?). To answer that:
There will be no second time offering for a class offered in the past. Every
time there will be a new class, I issue a new code. That means, the students
never take the same course again. However, I do need to be able to enter the
same student to a new course without having to re-enter their info. Also, as
far as the 3rd table, do I need two primary keys (one from student table
(SSN), and one from the other table?). Can you please explain the detail
steps of the 3rd table? That’s what confuses me. Thank you.





:

 

[Guest]

Few more things to ask:
What is the fastest way to grab the field information from both tables and
put them into the new 3rd table. Also is there a live discussion that we can
use to ask questions? Thank you.

 

[Douglas J Steele]

If the primary key for the Class table (let's call it ClassID, since you
haven't provided the name yet) uniquely identifies a particular offering of
a course, then all you should need as the primary key for StudentClass is
SSN and ClassID. By this, I mean the primary key contains both fields (you
can't have more than one primary key, but you can have up to 10 fields in a
single index)

You don't enter student information (nor class information) in StudentClass.
Because you've got the SSN for the student, you can always obtain whatever
student information you need by joining to the Student table. Similarly,
you'd join to the Class table if you wanted information about the course.
What you might store in the StudentClass would be the student's grade in the
course (or simply whether or not the student passed the course)

You're going to need a form/subform setup. How you do this would depend on
how you think of the data. If you want to start with a student and enrol the
student in specific courses, your form should be bound to the Student table.
If you want to start with a course, and pick what students are enrolled in
that course, your form should be bound to the Class table. Either way, your
subform would be bound to StudentClass, and you'd have to link the form and
subform appropriately. You can add a combo box in the subform that's bound
to "the other table" (i.e.: if your form is bound to Student, the combo box
in the subform would be bound to Class, and vice versa). In that way, you
don't need to know the ClassID of the particular class: you select the class
from the combo box, and the ClassID field in the StudentClass table gets
automatically populated.

Take a look at the Orders form in the Northwinds database that came with
Access for one example of what I'm talking about.

And, with respect to your other question, there may well be live discussion
locations, but I don't participate in them, so can't point you to any.

 

[Guest]

Doug,

Thank you for the reply. I just have few more questions.

1. Do I still need to include the field SSN (PK) from the tstudent into my
tclass, since I'm going to join them by the third table?

2. Once I create the third table, do I need to create an index that holds
both
fields (SSN, ClassID)? if so, how?

3. Also, you mentioned that I don't need to re-enter the information again
into the third table. Is that correct?

As always, your help is greatly appreciated Doug.

 

[Douglas J. Steele]

1. Do I still need to include the field SSN (PK) from the tstudent into
my
tclass, since I'm going to join them by the third table?

No. It serves no purpose.

2. Once I create the third table, do I need to create an index that holds
both
fields (SSN, ClassID)? if so, how?

Yes. With the table open in Design view, select the two fields and click on
the Key icon to create the primary key.

3. Also, you mentioned that I don't need to re-enter the information
again
into the third table. Is that correct?

Well, I suppose it depends on what definition of "re-enter" is. The only
values that are needed in the third table are SSN and ClassID. Assuming you
set your form and subform up correctly, you'll get one value supplied from
whichever entity is represented in the form, and you'll get the other value
from the combobox on your subform.