Solved Calculated field from marks

[Martynwheeler]

Hi,

Hopefully I can explain this clearly.

I am building a student marksheet database.

I have a student table and assessment table which are joined by student_assessment table where marks are stored.

Each assessment can have different grade boundaries. So I have a gradeset table with the fields

Id, A*, A, B, C, D, E, U
1,85,75,65,55,45,35,0
I can create a gradeset which gives the percentages needed for a grade. Many assessments use the same gradeset, but bigger exams have their own gradeset so I need to be flexible.

I include the gradeset Id as a fk in the assessment table.

We also standardise all tests on a unified scale so that we can compare and aggregate data. The score is
0,10,20,30,40,50,60,70
With the intervals corresponding to the grades U, E, D, C, B, A, A*

This system takes their percentage score and performs a calculation:

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

This allows different assessments to have different boundaries to allow for variation in difficulty, but places everything on the same scale.

I can do a query and get the percentage for each student for each test. I have a vba function that I pass the percentage and the assessment Id to which returns the unified score. I would like to display the calculated unified score. I can do this with my function using a small set of test data. But I realise I am querying the database everytime the function is called to get the grade boundaries from the gradeset table (this will be several thousand times with a real dataset).

So my question is can I do some kind of join to do the calculation using my query that gets the marks from the database. I am not sure that I can because of the way I have stored the grade boundaries as a single record in the gradeset table.

Hopefully this makes sense

Thanks, Martyn

 

[arnelgp]

there are 8 unified scales while your gradeset has 7 items.
to make it easy make them equal in size (7x7 or 8x8).

you can do it in sql ofcourse. but i prefer the vba using dictionary (see Query1)
the dictionary object is set to Nothing when you pass a -1 as id.
use UseThisForm form, so that the dictionary object is reset everytime you open this form.

 

[Martynwheeler]

Thank you. I will examine this later. The grades correspond the gaps so there will always be n-1.

 

[Martynwheeler]

I have looked the approach you have made. The vba function is making two queries every time it is called. Is this going to slow things down?

I realised that I can get the grade boundaries in my original query. Would it be more efficient to use those and pass them to my function to avoid additional queries?

Thanks for your help
Martyn

 

[ebs17]

Solution efficiency would start with YOU providing an example database (only with necessary content) because others could work with it immediately.

Not everyone is able to recreate such a database based on less or more vague descriptions, or rather people avoid this unnecessary effort.

 

[Martynwheeler]

Solution efficiency would start with YOU providing an example database (only with necessary content) because others could work with it immediately.

Not everyone is able to recreate such a database based on less or more vague descriptions, or rather people avoid this unnecessary effort.

I take your point. I will have to make a copy and empty my db to get rid of names.

I will do so when I get home.

Thank you for the suggestion

 

[MajP]

If you want a no code solution you likely need to normalize your data

tblGradSet
--gradeSetID
--GradSetName
-- maybe where used

tblGradeSetValues
--GradSetID_FK
--GradeSetLetter
--ScoreLow
--ScoewHigh

GradeSetID_FK	GradSetLetter	ScoreLow	ScoreHigh
1	A	85	100
1	A*	75	84
1	B	65	74
1	C	55	64
1	D	45	54
1	E	35	44
1	U	0	34

You can build a query with only the low range, but it is far more difficult.

 

[Martynwheeler]

If you want a no code solution you likely need to normalize your data

tblGradSet
--gradeSetID
--GradSetName
-- maybe where used

tblGradeSetValues
--GradSetID_FK
--GradeSetLetter
--ScoreLow
--ScoewHigh

GradeSetID_FK	GradSetLetter	ScoreLow	ScoreHigh
1	A	85	100
1	A*	75	84
1	B	65	74
1	C	55	64
1	D	45	54
1	E	35	44
1	U	0	43

You can build a query with only the low range, but it is far more difficult.

Right I am home now and I can see my mistake in trying to have a gradeset as a single record. I am going to do some re-organising of logic. Thanks for the idea

 

[Martynwheeler]

Right, I have anonymised the data and cut it down to two students. The database is attached.

I have the following query set up in design view:

the sql generated is

SELECT Student.student_id, Student.fullname, Assessment.title, Format([Student_has_assessment.mark]/[Assessment_has_Cycle.max_mark]*100,"#") AS pct_mark
FROM Student INNER JOIN (GradeSet INNER JOIN (Assessment INNER JOIN (Assessment_has_Cycle INNER JOIN Student_has_Assessment ON Assessment_has_Cycle.assessment_has_cycle_id = Student_has_Assessment.assessement_has_cycle_id) ON Assessment.assessment_id = Assessment_has_Cycle.assessment_id) ON GradeSet.gradeset_id = Assessment.gradeset_id) ON Student.student_id = Student_has_Assessment.student_id;

This yields the results

I'd like to use the gradeset to add grades to the query

A couple of explanation points:
The marks are entered as raw marks (for convenience). The assessment titles are the same each year but the number of marks can differ if a test is modified - so I have assigned an assessment set to a cycle (e.g. 2023/24) so that they can be retrieved in future. If there are other pointers I am happy to listen to wiser folk than me.

 

[Martynwheeler]

If you want a no code solution you likely need to normalize your data

tblGradSet
--gradeSetID
--GradSetName
-- maybe where used

tblGradeSetValues
--GradSetID_FK
--GradeSetLetter
--ScoreLow
--ScoewHigh

GradeSetID_FK	GradSetLetter	ScoreLow	ScoreHigh
1	A	85	100
1	A*	75	84
1	B	65	74
1	C	55	64
1	D	45	54
1	E	35	44
1	U	0	43

You can build a query with only the low range, but it is far more difficult.

Quick question, why are the ranges not continuous? ie, 75->85, 65->75, etc. What if a student has 84.5 %?

 

[MajP]

In your query you would do something where one side of the range will include an =.

where Score >= scorelow and Score < ScoreHigh

 

[Martynwheeler]

In your query you would do something like. One side of the range will include and =.

where Score >= scorelow and Score < ScoreHigh

Thanks so much. The normalisation tip was very useful. Hopefully the rest of tables and relationships are okay.

 

[LarryE]

Creating one of thse databases always follows the logic of the business process. In your case, the business process is tracking students, years, courses, subject skills sets and assessment of those skill sets as defined by a grading system. So you have:

1. Multiple students
2. Each student may have multiple school years of attendance (start 2022 and end 2023 for example)
3. Each year may offer multiple Subject courses (Physics for example)
4. Each Subject may have multiple Classes (12V/Ph1)
5. Each Class may have multiple individual Topic subjects taught (Independent thinking, Instruments and equipment)
6. Each Topic subject taught has multiple Skill assessments made (Apply investigative approaches and methods, Follow written instructions)
7. Each assessment may be made in multiple Grade Sets (Topic Tests or other assessment methods)
8. Each Grade Set assessment made has a Grade Set Value assigned.

Does this describe the process?

 

[Martynwheeler]

Creating one of thse databases always follows the logic of the business process. In your case, the business process is tracking students, years, courses, subject skills sets and assessment of those skill sets as defined by a grading system. So you have:

1. Multiple students
2. Each student may have multiple school years of attendance (start 2022 and end 2023 for example)
3. Each year may offer multiple Subject courses (Physics for example)
4. Each Subject may have multiple Classes (12V/Ph1)
5. Each Class may have multiple individual Topic subjects taught (Independent thinking, Instruments and equipment)
6. Each Topic subject taught has multiple Skill assessments made (Apply investigative approaches and methods, Follow written instructions)
7. Each assessment may be made in multiple Grade Sets (Topic Tests or other assessment methods)
8. Each Grade Set assessment made has a Grade Set Value assigned.

Does this describe the process?

Yes, that is a good description of the process.

 

[LarryE]

Looking at your table structure and field relationships, I am confused about why you need all the "has" tables? For example:
Assessment_has_Cycle
Student_has_Assessment
Subject_has_YearGroup

They appear to be Junction tables, but you don't need them to create a junction table between other tables.

It seems to me that a much less complex design, which would be easier to maintain and manage, might be something like this:

I am not sure where the Assessment table belongs in this design however.

 

[Martynwheeler]

Looking at your table structure and field relationships, I am confused about why you need all the "has" tables? For example:
Assessment_has_Cycle
Student_has_Assessment
Subject_has_YearGroup

They appear to be Junction tables, but you don't need them to create a junction table between other tables.

It seems to me that a much less complex design, which would be easier to maintain and manage, might be something like this:
View attachment 112256
I am not sure where the Assessment table belongs in this design however.

They are describing many-to-many relationships. Each subject can be in many year groups and each year group can contain many subjects.

Assessments can be different papers each year with different numbers of marks. Therefore I have a junction table as each assessment can be in many years(cycles) (with different papers) and each cycle has many assessments.

Student_has_assessment is describing the fact that each student has many assessments and each assessment has many students.

The skills and topics are for another part of the project and are not directly linked to students or assessments.

 

[LarryE]

They are describing many-to-many relationships. Each subject can be in many year groups and each year group can contain many subjects.

Assessments can be different papers each year with different numbers of marks. Therefore I have a junction table as each assessment can be in many years(cycles) (with different papers) and each cycle has many assessments.

Student_has_assessment is describing the fact that each student has many assessments and each assessment has many students.

The skills and topics are for another part of the project and are not directly linked to students or assessments.

OK as long as you got it solved.

 

[Martynwheeler]

OK as long as you got it solved.

I will think about your suggestions.

 

[Martynwheeler]

OK as long as you got it solved.

I have an email with a reply from you but I can't see it on the thread.?

 

[LarryE]

I have an email with a reply from you but I can't see it on the thread.?

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