Form based on Query

[Mary Edwards]

Hello! Please help!

I have set up a database to store and analyse children's grades. I have 1 table for each subject, and a table with the children's information on.
I need to set up a form that has a drop down box to select the child, and it bring up all the grades in all the subjects for that child.
There is a 1 to 1 relationship between the full name on the pupils table and the name on the subject table so there wont be duplicate records.

Thanks!

 

[Deleted member 147267]

A table for each subject suggests your db is not properly normalized, and this will continue to perplex you if that is the case. You should research normalization as it applies to your situation. Perhaps

http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/

If you think your db is normalized, maybe post a pic of your relationships window.

 

[Gasman]

As Micron indicates your StudentSubject Table should have a field for Subject, perhaps a FK from a real Subject table.?
You can create a unique index on Student and Subject fields to prevent duplicates.

 

[bob fitz]

Mary Edwards

I completely agree with the comments made by Micron regarding normalization. I have attached a db to illustrate how this might be done. Hope this helps

 

[arnelgp]

maybe this will give you an idea on how to setup your grade table.

 

[Mary Edwards]

Those are really helpful. Thanks
The reason we had a different table for each subject is because the children will have a max of 4 grades a year and we need to show and analyse the progress over the course of the year
ie Sept - D, Dec - C, Spring - B, Summer - A would be ideal progress. I have set up calculations to work out how many steps progress are made in each area). At this point I am looking at the "teachers input form" that has the 4 judgements for each area showing on 1 form. Searching by pupils name

 

[arnelgp]

You just add another field in grades table that i gave. This will signify which grading (d,c,b,a).
Therefore, you will have Duplicate student/subject but diff season.
It is easier to do calculation on single table rather on multiples.

 

[bob fitz]

Take a look at the attached db. Should do what you want.