Three-Way Join Tables

[Regulatory Affairs]

I'm creating a database to track student info, courses, grades, etc. In designing the tables, it appears a have three tables that relate to each other: students, courses (course & semester) and grades. Each student can have multiple courses and vice versa. Each student can have multiple grades, one for each course. I think I need a join table for the student - course many to many relationship, but am not sure what to do with the grades table. Any ideas?

[Ender]

Let's call the Student-Course join table a "CourseEnrollment" table.

If there is only one Grade per CourseEnrollment, then Grade would just be a field in CourseEnrollment. If you're tracking multiple assignments or test scores for each CourseEnrollment, and need a Grade for each, then an Assignment table and a Grade table (CourseEnrollment-Assignment join) would be used.

[Regulatory Affairs]

That sounds like it will work. Thanks for your help...and so quickly. Have a great day!

[Regulatory Affairs]

I've created the table to join the student and course info (Student-Course Join), but am having a problem displaying and creating records.

I have a form layout called Academic Info that should display the current student's courses, whether completed or not, in a portal. The fake data I'm using has one student with two courses, but they are displayed as two records on the layout, not two in the portal.

While on the Academic Info layout, Ctrl N creates a new record in the Student-Course Join table. I enter a Student Record ID, hit enter, and the correct name appears, but the portal fields are not editable.

I've attached a copy of the database. Any help would be GREATLY appreciated.

Reg_Aff_Students.zip

[Ender]

The layout should be based on the Student table, with the portal to the Student-Course join.

[Regulatory Affairs]

Thanks for the help. It takes care of the display problem, but I still have a problem creating a new record of a course for a student. I'm open to suggestions.

[Ender]

You can add them to the portal if you add the CourseID from the join table to the portal, and use that for selecting a Course (define a Courses value list if you wish).