Jump to content
Sign in to follow this  
Regulatory Affairs

Three-Way Join Tables

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.