How do I create multiple table relationships on Access 2016?

Question

I have a table that shows each student's details with their module choices for semester 1 under each student 1. I want to add the semester 2 module choices as well so they can be seen on the student detail table. Is there a way to do this? When I try to set up the relationships, the student details are shown in the semester 2 options table 2, which is not what I want. I haven't done that much on access so apologies if this is a simple question or if it isn't possible...

Answer 1

You already created relationships. As I understand, you want to see more than one "child" table in table designer. If so, it's not possible.

If you want to select options for different semesters, create a form based on Students table and two subforms for Semester1 and 2.

Also I would recommend to change database structure. You don't need two tables for semester modules. Create just one table with additional field like SemesterNumber and place subforms, based on this table with different criterias for SemesterNumber. And add own primary key for SemesterOptions, do not use StudentID as PK, actually you have one-to-one relationship, I believe you want select more than one module for each student.

Answer 2

In my opinion, you should have more tables, which in end will make it much easier to query data and get reports out of it, as well it will make it easier to create forms for each table to record data.

Here is simple table structure I would do for student/class registration.

You can also link tblSemester table to tblClass, if you like to make a list of classes offered each semester and make it bit more complicated, but with this organization you will be able to get list of all classes for student, or all student for particular class or semester, and with ease add more data later.