Solved Calculated field from marks

[Martynwheeler]

That's because I had second thoughts and deleted it. Sorry.

Ha ha, that is fine. I may have not set out my intentions clearly.

I am trying to make a student tracking system to replace our multiple spreadsheets.

Here are the intentions :

A student starts with us in Y12
They will study three subjects for two years.
At present I am sure only interested in my own subject (physics) but I would like to keep the flexibility to add others.

I would like to centralise our record keeping

A student will be enrolled in a physics class in Y12 and through the year do various assessments
Module tests and trial exams (graded on a A*-U) scale. Different boundaries depending on the test. The grade boundaries for these tests can change from one year to the next but the title of the assessment topic will remain the same. I'd need to be able to input and see the current cohort but also be able to look at previous cohorts.

They also have homework tasks that are not graded but need to be recorded.

In their second year they move between classes (one of the issues that causes a headache with spreadsheets). Here they do more predefined tests and exams which have the same variability with grades.

By the time they leave they will have done 20 tests.

We have six classes in Y12 and six classes in Y13. As I said students move between classes.

I can pull student and class enrollment from our registration system but it is useless for keeping assessment data.

I can easily populate my database with this data from a csv.

I need to be able to enter marks by class. And the view their marks and do various calculations to monitor and track progress and predict potential grades.

It can be done with spreadsheets - but it is messy hence why I have turned to access (I can't use an online approach due to data protection). I also can't use a software approach because It won't let any home made software on the network.

I am proficient with programming but not so much with db design. I have a big piece of paper with all the relationships sketched out. Which is what is in the db I attached.

The PAG stuff I related but a side project (we have to print out class tick lists to assess students carrying out practical work) the PAG stuff allows me to use the class list from the db to make the tick lists. That is why there are no relationships to the rest of the db (it jus creates empty tick sheets).

Hopefully that explains what I am trying to achieve.

Thank you for taking the time to read and help. I appreciate your input.

 

[LarryE]

In which table do you wish to assign a letter grade? Do you assign it to the class overall or do you assign it to each individual assessment item?

 

[LarryE]

I need to be able to enter marks by class

OK that makes a big difference.

 

[Martynwheeler]

I don't assign grades. I have grade boundaries for each test. I only store raw mark for each test in the table student_has_assesment. I then use a select query and assign grades when I need to see them (it seems redundant to store grades and it is an automated process this way).

 

[Martynwheeler]

OK that makes a big difference.

Sorry if I was not clear originally.
Does my approach make sense?

I have a working (sort of) system that produces a nice grade table. I have entered legacy data by unpivoting my spreadsheet and doing a copy paste. I have not really started on the UI yet. I am trying to get my database to be able to store information in a retrievable way.

It is only 250 students but it is a big job keeping track of everything.

In addition to entering marks by class I need to be able to view marks by
Class
Year group
And have the ability to do this for past data to make comparisons.
And apply filters based on sex, special needs status etc (all stored in the student table).

Thank you again
Martyn

 

[LarryE]

Sorry if I was not clear originally.
Does my approach make sense?

I have a working (sort of) system that produces a nice grade table. I have entered legacy data by unpivoting my spreadsheet and doing a copy paste. I have not really started on the UI yet. I am trying to get my database to be able to store information in a retrievable way.

It is only 250 students but it is a big job keeping track of everything.

OK. I'll see if I can understand more about this.

 

[LarryE]

Marty:
Here is the design where:

1. Each student and each cycle year have multiple subjects (each student can take multiple subjects in multiple years)
2. Each Subject may have multiple Classes
3. Each Class may have multiple GradeSets.
4. Each GradeSet may have multuple Assessments and GradeSetValues

This design will produce this query. I input Two, Student test percentages for illustrative purposes.

Does this look correct? The query shows the grade_letter for each Assessment record where the PercentMark is >=score_low AND <=score_high. I believe this is close to your requirement.

 

[Martynwheeler]

Marty:
Here is the design where:

1. Each student and each cycle year have multiple subjects (each student can take multiple subjects in multiple years)
2. Each Subject may have multiple Classes
3. Each Class may have multiple GradeSets.
4. Each GradeSet may have multuple Assessments and GradeSetValues

View attachment 112298
This design will produce this query. I input Two, Student test percentages for illustrative purposes.
View attachment 112299
Does this look correct? The query shows the grade_letter for each Assessment record where the PercentMark is >=score_low AND <=score_high. I believe this is close to your requirement.

Wow, that looks way simpler than my design and does exactly what I am trying to do! Would you mind sharing the database you have created please so that I can check it does everything?

 

[LarryE]

Wow, that looks way simpler than my design and does exactly what I am trying to do! Would you mind sharing the database you have created please so that I can check it does everything?

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

 

[Martynwheeler]

Marty:
Here is the design where:

1. Each student and each cycle year have multiple subjects (each student can take multiple subjects in multiple years)
2. Each Subject may have multiple Classes
3. Each Class may have multiple GradeSets.
4. Each GradeSet may have multuple Assessments and GradeSetValues

View attachment 112298
This design will produce this query. I input Two, Student test percentages for illustrative purposes.
View attachment 112299
Does this look correct? The query shows the grade_letter for each Assessment record where the PercentMark is >=score_low AND <=score_high. I believe this is close to your requirement.

The only thing I can't see is how do year groups fall into this? In each cycle year (e.g 2023/2024) a subject will have a set of year12 classes and year13 classes.

 

[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

Thank you Larry, it is very kind of you to help.

 

[LarryE]

The only thing I can't see is how do year groups fall into this? In each cycle year (e.g 2023/2024) a subject will have a set of year12 classes and year13 classes.

Simply add a Year_Group_No field to your Subject table with a Number Datatype. Then you can just enter 12 or 13 for each Subject for each cycle. You don't need a separate table for that. When you create your Subject input form, a drop-down list box can show 12 and 13 so you (or any user) can select which one is applicable. So you will have Physics 12, Physics 13, English History 12, English History 13 etc.

 

[Martynwheeler]

Simply add a Year_Group_No field to your Subject table with a Number Datatype. Then you can just enter 12 or 13 for each Subject for each cycle. You don't need a separate table for that. When you create your Subject input form, a drop-down list box can show 12 and 13 so you (or any user) can select which one is applicable. So you will have Physics 12, Physics 13, English History 12, English History 13 etc.

Ah, that makes sense. I will take a look at it in detail tomorrow evening. Once again thank you for helping

 

[MajP]

I realized that I told you wrong about the intervals.
Your scores are really
Id, A*, A, B, C, D, E, U
1,85,75,65,55,45,35,

85 <= Score <=100 A*
75 <= Score < 85 A
65 <= Score < 75 B
55 <= Score < 65 C
45 <= Score < 55 D
35 <= Score < 45 E
0 <= Score < 35

The problem is either the upper or lower range. In this case the top range has a <= instead of a hard <. Lets assume there is no extra credit and the top score is 100. The trick is to make the ranges like this and just make the top range large.

tblGradeScaleIntervals tblGradeScaleIntervals

IntervalID	GradeScaleID_FK	LetterGrade	RangeLow	RangeHigh
1​	1​	A	85​	101​
2​	1​	A*	75​	85​
3​	1​	B	65​	75​
4​	1​	C	55​	65​
5​	1​	D	45​	55​
6​	1​	E	35​	45​
7​	1​	U	0​	35​

Now you could have another table for the different types of scales you might use.

tblGradeScale tblGradeScale

GradeScaleID	GradeScaleName
1​	Standard Scale
2​	Finals Scale

To test you want to test the boundary conditions. Values equal, just above, and just below.

tblScores tblScores

ScoreID	ScaleID_FK	Score
1​	1​	85.1​
2​	1​	85​
3​	1​	84.9​
4​	1​	0.1​
5​	1​	0​
6​	2​	100​
7​	2​	90.1​
8​	2​	90​
9​	2​	89.9​
10​	2​	80​
11​	2​	60​

The table have boundary conditions for both scales.
In a query

SELECT tblgradescale.gradescalename,
       tblscores.score,
       tblgradescaleintervals.rangelow,
       tblgradescaleintervals.rangehigh,
       tblgradescaleintervals.lettergrade
FROM   tblgradescale
       INNER JOIN (tblscores
                   INNER JOIN tblgradescaleintervals
                           ON tblscores.scaleid_fk =
                              tblgradescaleintervals.gradescaleid_fk)
               ON tblgradescale.gradescaleid = tblscores.scaleid_fk
WHERE  (( ( tblscores.score ) >= [rangelow]
          AND ( tblscores.score ) < [rangehigh] ));

That gives you solutions like
qryDemoGrades qryDemoGrades

GradeScaleName	Score	RangeLow	RangeHigh	LetterGrade
Standard Scale	85.1​	85​	101​	A
Standard Scale	85​	85​	101​	A
Standard Scale	84.9​	75​	85​	A*
Standard Scale	0.1​	0​	35​	U
Standard Scale	0​	0​	35​	U
Finals Scale	100​	90​	101​	A
Finals Scale	90.1​	90​	101​	A
Finals Scale	90​	90​	101​	A
Finals Scale	89.9​	80​	90​	B
Finals Scale	80​	80​	90​	B
Finals Scale	60​	60​	70​	D

Now you can make a function to use throughout the application.

Public Function GetGrade(ScaleID As Long, Score As Double) As String
  If IsNumeric(ScaleID) And IsNumeric(Score) Then
    If Score >= 0 And Score <= 100 Then
      GetGrade = DLookup("letterGrade", "tblGradeScaleIntervals", Score & " >= rangeLow AND " & Score & " < rangeHigh AND GradeScaleID_FK = " & ScaleID)
    End If
  End If
End Function

Pass in which scale and the score and get the grade.

 

[MajP]

Where it can get tricky is when you have unbounded intervals on both ends and you do not now really how far they can be unbounded. Most of the time this can be solved with just putting a really big / small number at the end points. Imagine this was something besides grades where you could have really small or really large outliers, but most things fall into some increments.
tblGradeScaleIntervals tblGradeScaleIntervals

RangeLow	RangeHigh
85​	2147483647​
75​	85​
65​	75​
55​	65​
45​	55​
35​	45​
-2147483648​	35​

In this case it is basically catch all for the end values
>=85
and
< 35

Since my ranges used longs these are the biggest possible longs. If outliers could go bigger than that you could use a double for your ranges which is basically +/- 1.797 x 10308

 

[Martynwheeler]

Where it can get tricky is when you have unbounded intervals on both ends and you do not now really how far they can be unbounded. Most of the time this can be solved with just putting a really big / small number at the end points. Imagine this was something besides grades where you could have really small or really large outliers, but most things fall into some increments.
tblGradeScaleIntervals tblGradeScaleIntervals

RangeLow	RangeHigh
85​	2147483647​
75​	85​
65​	75​
55​	65​
45​	55​
35​	45​
-2147483648​	35​

In this case it is basically catch all for the end values
>=85
and
< 35

Since my ranges used longs these are the biggest possible longs. If outliers could go bigger than that you could use a double for your ranges which is basically +/- 1.797 x 10308

Thanks for the tips

 

[arnelgp]

this a demo db, maybe you can get idea on it.

 

[Martynwheeler]

this a demo db, maybe you can get idea on it.

Thank you, that is essentially the model I was working on.

 

[arnelgp]

better for everyone to know, so they can only add functionality on your existing db and not to start from scratch.

 

[Martynwheeler]

Simply add a Year_Group_No field to your Subject table with a Number Datatype. Then you can just enter 12 or 13 for each Subject for each cycle. You don't need a separate table for that. When you create your Subject input form, a drop-down list box can show 12 and 13 so you (or any user) can select which one is applicable. So you will have Physics 12, Physics 13, English History 12, English History 13 etc.

better for everyone to know, so they can only add functionality on your existing db and not to start from scratch.

The two approaches will lead to the same end result. The use of junction tables for student_class or student_grade seems to be the standard model on introductory websites on relational db. Larry's approach seems very different and uses fewer tables and relationships to achieve the same end result. I am definitely finding it harder to visualise this approach.