Solved Calculated field from marks (1 Viewer)

[Martynwheeler]

Of course. I have deleted all the queries, modules, forms and reports so it is just tables and the one query for test purposes. Maybe you can work with the design and make it work for you. It has the same name so you may want to name it differently when you save it so you don't overwrite your old file. Happy to help further if I can.

Good luck.
Larry

Hi Larry,

The bit that confused me is how the students are assigned to a class. In the class table, one student has 12 entries (all of which are physics classes), but in reality they can only exist in one class at a time. I can later that they only one class in the gradeset table. I can't quite get my head around this. Especially as I have to upload the class info from a csv file along with the student info.

Thank you
Martyn

 

[LarryE]

Hi Larry,

The bit that confused me is how the students are assigned to a class. In the class table, one student has 12 entries (all of which are physics classes), but in reality they can only exist in one class at a time. I can later that they only one class in the gradeset table. I can't quite get my head around this. Especially as I have to upload the class info from a csv file along with the student info.

Thank you
Martyn

That's because you had 12 different classes all assigned to the Physics Subject and 1 student assigned to Physics. Martin, I have been designed a new one with the following relationships:

This is a much cleaner design and will allow for better use when you input grade/mark data. I have also designed some data input forms and have been testing input. I discovered that you had some fields set as Required and some set for No Duplicates Allowed, which caused major problems when I attempted to input data. I corrected those problems and can now input test data. No Duplicates Allowed is ONLY for Primary Keys and nothing else.

This application will be very complex due to the fact that you wanted to not only use different Assessment criteria for different Classes, but also different Grade Sets and different Grade Set Values.

As to assigned students to Subject Classes, I have no clue how this cvs file is formatted or what information it contains or which fields relate to which tables. Only you know that.

You have many students
Studying several Subjects
Each with several classes
Each may have different Grade Sets
Each Grade Set has an Assessment and Grade Set Values set

It's complicated. I am going to give you the file with input forms that I made. It is VERY PRELIMINARY. Go ahead and review it, but remember the Female student has 12 classes assigned at the moment.

 

[Martynwheeler]

Thank you. I will take a look properly over teh weekend. BTW, I can't see the tables/forms/queries on the left of the screen. how did you hide them?

Martyn

 

[LarryE]

Thank you. I will take a look properly over teh weekend. BTW, I can't see the tables/forms/queries on the left of the screen. how did you hide them?

Martyn

F11 to get the navigation bar to show.
Delete all the files I sent you, they are wrong. That is not the design that will work. I guess it's just old age or senility setting in.

Imagine a brand new school with no students yet. You still need Subjects, Classes, Grade Sets and Assessments no matter what cycle year it is.
Now add students and cycle years. Each student gets assessments for each Classes Grade Sets (Topic Test for example) each cycle year.

This needs further thought.

Have a good weekend. I'm going to watch some Midsomer Murders. For a small county, Causten sure has a lot of murders.

 

[Martynwheeler]

F11 to get the navigation bar to show.
Delete all the files I sent you, they are wrong. That is not the design that will work. I guess it's just old age or senility setting in.

Imagine a brand new school with no students yet. You still need Subjects, Classes, Grade Sets and Assessments no matter what cycle year it is.
Now add students and cycle years. Each student gets assessments for each Classes Grade Sets (Topic Test for example) each cycle year.

This needs further thought.

Have a good weekend. I'm going to watch some Midsomer Murders. For a small county, Causten sure has a lot of murders.

Hey Larry,

I know the feeling about old age....

I think I have come up with a design that works. I had some time while a class was doing a test. I took on board the suggestions from your posts got rid most of the junction tables. I now have assessments belonging to subjects. I can easily import/update from my csv. I should be able to edit marks easily and display the grades. I just need to test the idea with some test data. I will post my design later.

Once again thank you for your advice. Enjoy Midsomer Murders.

Martyn

 

[Martynwheeler]

Hi Larry,

Here is my redesign.

It seems to me to be more logical.

The student_has_subject junction table expresses the relationship
Each student can have many subjects
Each subject can have many students

The primary key of this junction table is an instance of a student_subject (these are unique because a student can only be in a subject once). The remaining fields in this junction table then express the class, year group and academic cycle the student is currently registered into - these change once per year as the student data is imported from my external file. Once a student no longer appears in my external file (because they have left) their data will no longer be updated but will remain as their last class, year group and academic cycle - this allows me to view data for students who have left.

Not shown on here are three tables containing ids for class, year group and academic cycle - I realise that these values could be directly placed into the junction table but I will need the values to populate comboboxes on forms to allow me to select a class/year/etc.

The crucial bit is now the assessments belong to a particular instance of a student_subject. This table will store marks for a student_subject for each possible assessment they take.

Since we store raw marks (saves calculating percentages for every student after adding marks for a test), I also store the max mark for the assessment and details of how it is graded. This can now change every year because I have a table (set up at the beginning of the year as part of the roll over of students) which stores the current max_marks and gradeset_id.

I have created a form that can be used to edit/add student marks for a test (key requirement of the design - this is like adding data into our old spreadsheet).

I have a query that can grab all the student data and display it in a form that allows me to see the progress of students (by class or year).

I have a form that can import student data from my school database (samples included in the zip file) - this populates the student table and updates the enrolment.

See what you think. I may have missed something obvious.

Thanks for reading,

Martyn

 

[CJ_London]

Couple of comments

include a pk field in student has subjects table and leave the other two fields as indexed, no duplicates and add a fk field to the studentsubject table to join it / you can then delete the student and subject id fields

also suspect the subject table needs an academic year field or similar

 

[Martynwheeler]

Couple of comments

include a pk field in student has subjects table and leave the other two fields as indexed, no duplicates and add a fk field to the studentsubject table to join it / you can then delete the student and subject id fields

also suspect the subject table needs an academic year field or similar

Thank you. I was told by another member to only use no duplicates for primary keys. So i thought that composite PK was better in this regard.

 

[CJ_London]

Thank you. I was told by another member to only use no duplicates for primary keys

They are wrong -pk’s have to be no duplicates, other field or composite indexes can be unique or not as required

 

[LarryE]

Hi Larry,

Here is my redesign.
View attachment 112355

It seems to me to be more logical.

The student_has_subject junction table expresses the relationship
Each student can have many subjects
Each subject can have many students

The primary key of this junction table is an instance of a student_subject (these are unique because a student can only be in a subject once). The remaining fields in this junction table then express the class, year group and academic cycle the student is currently registered into - these change once per year as the student data is imported from my external file. Once a student no longer appears in my external file (because they have left) their data will no longer be updated but will remain as their last class, year group and academic cycle - this allows me to view data for students who have left.

Not shown on here are three tables containing ids for class, year group and academic cycle - I realise that these values could be directly placed into the junction table but I will need the values to populate comboboxes on forms to allow me to select a class/year/etc.

The crucial bit is now the assessments belong to a particular instance of a student_subject. This table will store marks for a student_subject for each possible assessment they take.

Since we store raw marks (saves calculating percentages for every student after adding marks for a test), I also store the max mark for the assessment and details of how it is graded. This can now change every year because I have a table (set up at the beginning of the year as part of the roll over of students) which stores the current max_marks and gradeset_id.

I have created a form that can be used to edit/add student marks for a test (key requirement of the design - this is like adding data into our old spreadsheet).

I have a query that can grab all the student data and display it in a form that allows me to see the progress of students (by class or year).

I have a form that can import student data from my school database (samples included in the zip file) - this populates the student table and updates the enrolment.

See what you think. I may have missed something obvious.

Thanks for reading,

Martyn

If this design works for you and you are able to view, edit, add and delete records, then this is the design you should use. As i have indicated before, I don't use separate junction tables. I join table fields to one another directly, but if this works then it is OK.

When you design these kinds of complex applications, you need to consider how forms will be used and make sure data is easily viewed and can be edited. I hope this design will be satisfactory and work for you.

 

[Martynwheeler]

They are wrong -pk’s have to be no duplicates, other field or composite indexes can be unique or not as required

Couple of comments

include a pk field in student has subjects table and leave the other two fields as indexed, no duplicates and add a fk field to the studentsubject table to join it / you can then delete the student and subject id fields

also suspect the subject table needs an academic year field or similar

If this design works for you and you are able to view, edit, add and delete records, then this is the design you should use. AS i have indicated before, I don't use seperate junction tables. I join fields to one another directly, but if this works then it is OK.

When you design these kinds of complex applications, you need to consider how forms will be used and make sure data is easily viewed and can be edited. I hope this design will be satisfactory and work for you.

Thanks for your help Larry. Reducing the complexity has made things much simpler and (so far) I have been able to implement all the functionality I need.

 

[LarryE]

Thanks for your help Larry. Reducing the complexity has made things much simpler and (so far) I have been able to implement all the functionality I need.

Very good. I was curious as to how this system works because we don't have this compexity in our education system here in the US. I went over all your previous posts and tried to learn all I could about how this system works.

 

[LarryE]

Unified score = "unified boundary below their grade percentage" + 10 * (percentage above the lower boundary) /(percentage difference between lower next boundary)

So, for example, if someone scored 67 % and the boundary for a B is 65 and an A is 75 their unified score would be

40 + 10 * (67 - 65)/(75 - 65) = 42

@Martynwheeler :
If you are still watching your thread, how have you calculated this? Is this calculation completed for each student for each assessment?
Just wondering.
Larry

 

[Martynwheeler]

@Martynwheeler :
If you are still watching your thread, how have you calculated this? Is this calculation completed for each student for each assessment?
Just wondering.
Larry

It is calculated in a query.

The mark is turned into a percentage. I join this to a table containing the grade sets and use a where >= and < the desired mark. It took a little getting my head around but it all works. The db contains sensitive info at the moment but I can create some sample anonymous data if you wish to take a look.

Martyn

 

[LarryE]

It is calculated in a query.

The mark is turned into a percentage. I join this to a table containing the grade sets and use a where >= and < the desired mark. It took a little getting my head around but it all works. The db contains sensitive info at the moment but I can create some sample anonymous data if you wish to take a look.

Martyn

No need. I was able to calculate it too. I used it on an input form where students and assessments and scores are displayed. I must admit it took me a while to get it. I noticed not all students with assesment scores will have a Unified Score. I just used 0 for them. I don't know how you handle that.

Using this design:

I am not done just having fun with it and the forms are not all functional yet, but I was just doing this for the challenge and fun of it. I am not sure if the design will really work, but I use very simple designs and let ACCESS do all the real work in my forms. I use a lot of forms and sub-forms linked with Master/Child fields where possible. Glad you have it working for you.

 

[Martynwheeler]

No need. I was able to calculate it too. I used it on an input form where students and assessments and scores are displayed. I must admit it took me a while to get it. I noticed not all students with assesment scores will have a Unified Score. I just used 0 for them. I don't know how you handle that.
View attachment 112499
Using this design:
View attachment 112500
I am not done just having fun with it and the forms are not all functional yet, but I was just doing this for the challenge and fun of it. I am not sure if the design will really work, but I use very simple designs and let ACCESS do all the real work in my forms. I use a lot of forms and sub-forms linked with Master/Child fields where possible. Glad you have it working for you.

Looks really nice Larry

 

[LarryE]

Martyn:
Does every student take every subject class in every group level 12 and 13? Or do some students NOT take some classes?

I've been able to work out how to use previously defined Grade Sets (Assessment criteria and related grade set values ) to be used for new Grade Sets and new classes, so for each class grade set, the instuctor could use a previously defined one or create and new blank one. And then for each classes grade set assessment, you can assign all students or invidual students. You said previously that you needed that flexibility.

I am testing these things presently. I'll let you know how it turns out. I was wondering if you have been able to do the same?

 

[Martynwheeler]

Martyn:
Does every student take every subject class in every group level 12 and 13? Or do some students NOT take some classes?

I've been able to work out how to use previously defined Grade Sets (Assessment criteria and related grade set values ) to be used for new Grade Sets and new classes, so for each class grade set, the instuctor could use a previously defined one or create and new blank one. And then for each classes grade set assessment, you can assign all students or invidual students. You said previously that you needed that flexibility.

I am testing these things presently. I'll let you know how it turns out. I was wondering if you have been able to do the same?

No each student is assigned to one class (group) in 12 and another in 13.

I think I have a working system now. I am getting colleagues to test it next week

 

[LarryE]

No each student is assigned to one class (group) in 12 and another in 13.

I think I have a working system now. I am getting colleagues to test it next week

So each student could be studying a Physics class in group level 12 and History class in group level 13 or maybe a group level 12 Physics class and a group level 13 Physics class? OK I see.

 

[Martynwheeler]

So each student could be studying a Physics class in group level 12 and History class in group level 13 or maybe a group level 12 Physics class and a group level 13 Physics class? OK I see.

Yes, students can be studying multiple subjects. There would be multiple classes for a given subject within a year group