Getting Two Existing Tables to Relate in a Form.

[Guest]

I have a form that displays records from two tables. The first table is named
Students. The second table is named Classes.

At the top of the form the Student table information displays. At the bottom
of the form the classes the student has taken should display. However, when I
go to the next student in the form the classes do not change at the bottom.

There's no common key between the two tables so I know that's the problem.
But since these tables (which have many records) and form already exist how
do I solve this problem?

Thank you in advance!

 

[Rick B]

Do you have a third table where you specify which classes each student has
taken? This is a Many-to-Many relationship and it requires at least three
tables.

 

[Guest]

No Rick I do not have a third table. I'm wondering if creating an AutoNumber
in each of the two tables then making it the Primary Key for them to link on.
Sounds too simple though and I'm new to this...

 

[Rick B]

You are losing me.

You have a list of people in one table.

You have a list of available classes in another table.

Now, you need a new table to tell it which people took which class. John
might have taken PSYC2301 in Fall of 2005 and ENG1301 in Spring of 2005.
Phil might have taken PSYCH23020 in Fall of 2005 and ENGL1301 and MATH1301
in Spring of 2005. Etc.

You have to have a third table in this case, unless your structure is flawed
in some way.

 

[Rick B]

Table1
StudentNumber
FirstName
LastName
SS#
etc.

Table2
ClassNumber
ClassName
etc.


Table3
StudentNumber
ClassNumber
DateCompleted
ClassGrade

 

[Guest]

Thanks Rick...your explanation makes perfect sense.