Linking multiple tables in a many-to-many relationship using query to achieve auto-fill forms

[student_2744]

Greetings,
MS Acess noob here.

I am creating a student management system database using Microsoft Access and I need help.


The image above summarizes all the relationships that I have between my tables. I am aiming to create a student management database in which users are allowed to add new students, subjects, teachers, or marks (meaning assesment grades) to the data.

Now, I have the basics figured out. As can be seen from above, I have created a many-to-many relationship between Students and Subjects, where many students can have many subjects and vice versa. My problem is that I now want to link the explained relationship to my tblMarkssssss (you can just call it tblMarks long story). So, my aim is that when a student_ID is selected for a Markssssss_ID from a form or somewhere, fields automatically appear that specify what each of the 6 subjects of the student is. To give an example, if Julie Andrews took subjects A,B,C,D,E,F, when her ID/ Name is selected in the tblMarkssssss, there will be an auto-fill in the fields (established by a relationship between tblStudents, tblSubjects, tblMarkssssss) specifying the name of each subject of the tblMarkssssss.

I feel like I am almost 100% missing fields in my tblMarkssssss, but I generally do not know what path to follow to achieve my aim. Alsp, my limited knowledge of MS Access yields that a query will almost most certainly be involved in this process. Hence my posting of this thread in the Queries subcategory.



If what I am asking for is practical for a database, any help as to how I can go about creating this relationship would be welcomed. If not, a nudge in the right direction would be appreciated.

If you have any questions, I am here to elaborate. Thank you in advance for all the help.

[June7]

tblMarks is not a normalized data structure. Why isn't term and grade maintained in Students2Subjects?

Students2Subjects should NOT have name fields.

[orange]

You may get some ideas from this Student marking data model from Barry Williams' site.

Here is a link to a Normalization article.

Good luck with your project.

[student_2744]

So you're saying that my junction table Students2Subjects should have the term and grade fields so that I can link it to tblmarks by adding field relationships?

Thank you for the resources.

[June7]

I could be wrong about where Term fields should be located. Explain what they are for.

Multiple similar name fields indicates non-normalized structure.

I had hard time reading that image and just noticed you have 6 sets of 3 associated fields. At first I thought that was 18 distinct Subject fields. Either way, this is not normalized. What if you need a 7th set or more someday?

What do M, P, C represent?

Quite possibly tblMarks should link to or replace Students2Subjects. Can student receive multiple marks for a subject?