Forms and Tables

[Guest]

I have a database that holds information about students.
I have several forms I use to create a class and enter the student into the
class.
And I have another main form that hold all the personal information with
another tab that displays the student's class.

My goal is to be able to enter the student prevously entered into another
class without re-entering the personal information.
To give you an idea of what I've done:

I have created a table (tstudent - with all the personal information about
the student with SSN as its primary key)

I have created another table (tclass - with all the fields about the classes
and start and end date and also added the field SSN from the tstudent table
(to relate) and its own primary key ID field)

I have also created another table called it tstudentclass - with the primary
fields of each of the above tables. This is where things get nasty. I just
have no clue what to do? How to create the relationship to see the enfinity
symble. And then how to proceed towards the forms? Any help or suggestion
is great. Thank you.

 

[Nikos Yannacopoulos]

Students and Classes is my favourite example for explaining many-to-many
relationships!

I have created a table (tstudent - with all the personal information about
the student with SSN as its primary key) Good.


I have created another table (tclass - with all the fields about the classes
and start and end date and also added the field SSN from the tstudent table
(to relate) and its own primary key ID field)

Good, except there should be no SSN field here. Just one record per
class and a unique ClassID - I bet this is what causes you all the
frustration.

I have also created another table called it tstudentclass - with the primary
fields of each of the above tables.

Good. That's the way to deal with it.

Forms:

The classes form is plain and simple, based on tclass table; I
understand you already have that, just lose the SSN control, like you
removed the SSN field from the table.

The student form must comprise a main form section based on the tstudent
table, and a subform based on tstudentclass. Start by making the subform
as an independent form, then when you add that as a subform in the main
student form the wizard will ask you which fields to join the two on;
select the studentID field on either side, and the job is done.

HTH,
Nikos