Solved Showing/Adding Absent Students in Exam Results (1 Viewer)

[Pac-Man]

Hello,

In a student database, result of subjects is entered as numeric data and same is the data type of ObtainedMarks field in the tblResult. Then marks of are summed up using either Sum formula or using Total row in query. What it is desired that when a student was absent in a specific paper, his result for that subject should show "A" or "L" (A for absent and L for leave). How can it be done since ObtainedMarks is a numeric field. What is a better way to do this? Should I changed data type of marks foeld to text?

Best Regards

 

[Gasman]

Whether they are A or L the result is 0 ?
If you want to determine A or L, I would have thought you would need another field? Then perhaps use P for present. Little redundant I know, as a mark indicates they were present anyway, but someone could get zero marks?

 

[Pac-Man]

someone could get zero marks?

Yes, plus only zero don't give indication of whether student was absent or on leave. Additionally, while entering marks in the result form, how to handle it there too? I mean if data type of ObtainedMarks is changed to text then user can enter A or L and same could be shown in result report but that too create problem for arithmetic operations.

 

[isladogs]

No need to enter the absent students manually.
You can add the absent students by using an unmatched query on tblStudents and tblStudenMarks. There is a wizard for that purpose.

 

[arnelgp]

if you are a teacher, you know that you also record each student whether present or absent.
you Must record it, same as you record it in the Attendance form manually.
there are Records for Attendance and there are different Records for Exams.

so you can Join the Two tables to show why a student got 0 (because of Absent?)

 

[CJ_London]

To determine the difference between leave and just absent, you need a leave table

Number fields have 4 format states - positive, negative, zero and null and you can use those states to show text.

so assuming your values are score (positive), leave (negative) and absent (zero), you would format with

0;”L”;”A”

 

[Pac-Man]

First of all I'm grateful to everyone for replying.

No need to enter the absent students manually.
You can add the absent students by using an unmatched query on tblStudents and tblStudenMarks.

Do u mean that absent student won't have a records for specific subject's exam and I can use that info to mark absent in result report?

you know that you also record each student whether present or absent.
you Must record it, same as you record it in the Attendance form manually.
there are Records for Attendance and there are different Records for Exams.

Yes I record daily attendance of every student but my result table don't have exam date right now so linking of attendance table with result table will need complete rework of result report because it has dynamic cross tab query (which took me a lot of time and effort to make it work). If I get sufficient time, I will try to do it.

Number fields have 4 format states - positive, negative, zero and null and you can use those states to show text.

so assuming your values are score (positive), leave (negative) and absent (zero), you would format with

0;”L”;”A”

With my current setup, I think it is doable (for manual marking absent/leave and then showng in result report) with little effort. But what happend with negative number in total marks calculation? Can I enter -0 and +0? And also what about those student who scored 0 in any subject?


Thank you very again for reply.

Best Regards

 

[CJ_London]

Good points

instead of 0 for absent, use null

how do you end up with negative total marks?

 

[Pac-Man]

how do you end up with negative total marks?

I mean if I mark student absent by giving him -1 score then total score (SumOfObtainedMarks) will also be reduced by 1 since we entered -1 for MarksObtained to mark him absent.

 

[isladogs]

@Pac-Man
You suggested in post #1 that absent students wouldn't have a blank in the exam mark sheet.
My response was based on that comment
Suggest you check your database to find out for certain.

 

[Pac-Man]

@Pac-Man
You suggested in post #1 that absent students wouldn't have a blank in the exam mark sheet.
My response was based on that comment
Suggest you check your database to find out for certain.

Pardon me if there is confusion due to my comment. My goal is to show students absent or On leave (by A & L abbreviation) in the result report. That can be done that can be done either automatically based on linking attendance table and result table as suggested by you and @arnelgp or manually at the time of entering subject wise result by using 0, blank or negative marks as suggested by @CJ_London but that has to be taken care at the time of calculating totals and percentages).

 

[CJ_London]

I mean if I mark student absent by giving him -1 score then total score (SumOfObtainedMarks) will also be reduced by 1 since we entered -1 for MarksObtained to mark him absent.

OK so -1 means absent and 0 means Leave

0;”A”;”L”

 

[Pat Hartman]

You can't use the grade to mean something else. The grade is the grade and as CJ initially recommended, the default should be null. Zero is a valid value so how can you distinguish an earned zero from not taken? You can't. Not to mention that in a relational database, each field is atomic. It holds one and only one piece of data. If you store 0 for leave and -1 for not taken, you mess up your average grade. Do NOT do this under any conditions.

Assuming you have an attendance table, you can join the student to the attendance table to determine if they were absent or on leave that day. That leaves your grade value clean.

For reporting purposes, you would use a left join of the student table to the exam table to ensure you have included all students. Assuming you leave the default for the grade as null, this will not mess up your average:

The average of 3, 0, 3 is 2 but the average of 3, null, 3 is 3. Null is ignored when calculating aggregate values in a query.

 

[CJ_London]

Don't disagree with what Pat is saying but you can exclude the -1 and null records if necessary or apply a different value if relevant to your marking

 

[Pat Hartman]

Rather than get into a fight regarding recommending bad solutions and then doubling down, I'll leave you to it CJ. I pointed out how to get the desired values from the attendance table without corrupting the meaning of the grade field which I'm sure the OP will now ignore so have at it. You should also tell him that if he uses your suggestion, it might actually conflict with the data in the attendance table.

 

[piano_playr]

The average of 3, 0, 3 is 2 but the average of 3, null, 3 is 3. Null is ignored when calculating aggregate values in a query.

I agree with Pat's preference to use null as an indicator that the student had an excused absence on exam day. One can calculate an average of integer values where one of the values in the series is null. As Pat said, that's a better option than giving the person a zero. The zero is going to unfairly pull down the average. In Pat's example, this calculation avg(3, Null, 3, 3) = 3 is better than avg(3, 0, 3, 3) = 2.25. I would count an unexcused absence as zero. However, that gets into the business logic of grading exams (e.g. do all the exams have the same weight? does the final exam have a larger weight than other exams?). It's possible to write some functions for your calculations if you want to get into some complex logic. I can see passing params to the function and then a nifty select statement enforces your business logic.

 

[Pac-Man]

Thanks for such detail replies @Pat Hartman and @piano_playr. I didn't know before that avg of 3,03 and 3,Null,3 will be different but yes I was worried about total marks as -1 would reduce total marks by 1. I think I will have to write function to consider only positive marks for getting totals and grades. At the later stage, I will have to modify structure of my results table to incorporate automatic indication of absent and leave as suggested in earlier posts of this thread but that will take time. I will try method suggested by CJ to check if I can make it work for manual input with less effort. If successful, I'll use it as a temporary solution or will wait to get sufficient time to make automatic method work.

 

[Pac-Man]

I also have plan to add another table and related forms to make datesheet (dates of each subject exam) and that could also be used to link result table and attendance table.

 

[Pat Hartman]

I think I will have to write function to consider only positive marks for getting totals and grades.

@Pac-Man ONLY if you follow the BAD advice to corrupt the meaning of your grade field. I gave you one solution that should work with your existing tables. If you are not already storing attendance information by day, you should be. But if you don't understand how to use that solution, just add a new column to your grade table. That suggestion is also BAD advice but it is less bad than corrupting a field by using it for two purposes. Why is my "add a new column" advice bad? It is bad because it duplicates data that belongs elsewhere (in the attendance table) and by duplicating it, you can cause a conflict. What if you mark the student present in the attendance table but mark the new field in the grade table as "A"? Maybe this is logical to you. Maybe if the student was present for part of a day, you want to mark him present for purposes of attendance but maybe he left early and so you want to mark him absent for purposes of the exam? I would not allow that conflict to happen without also adding a notes field to the grade table and forcing it to be populated if you have a conflict between the two tables.

NEVER corrupt the meaning of a field by overloading it with some other piece of information. You are already thinking about how you are going to get around the bad data. The solution is easy. Just don't make the data bad to begin with.

NEVER is certainly a strong word especially when capitalized. But you are in control of the schema and it is up to you to ensure that it is sound. When you use one field to have multiple meanings, you end up where we started this post by constantly having to compensate. Why would you ever want to do that when it is so easy to solve this problem correctly and avoid future problems?

PS, I am only replying because you addressed me directly. Otherwise, the person who gave you the bad advice should give you the solution to the bad advice. And then the solution to the solution to the bad advice. We all occasionally give bad or wrong advice but we don't usually double down on it when someone points it out.

I could write for hours on the proper situational use of null as the default for numeric values. Novices have trouble understanding null and compensating when necessary but this particular situation (exam grades) is one where Null is going to save you trouble rather than causing trouble primarily because of the way Access ignores null values when calculating aggregate values which is exactly what you want to happen. You don't need to write functions to ignore zeros so they don't affect grades because some grades will be zero because that is the grade the student earned.

 

[Pac-Man]

@Pac-Man ONLY if you follow the BAD advice to corrupt the meaning of your grade field. I gave you one solution that should work with your existing tables. If you are not already storing attendance information by day, you should be. But if you don't understand how to use that solution, just add a new column to your grade table. That suggestion is also BAD advice but it is less bad than corrupting a field by using it for two purposes. Why is my "add a new column" advice bad? It is bad because it duplicates data that belongs elsewhere (in the attendance table) and by duplicating it, you can cause a conflict. What if you mark the student present in the attendance table but mark the new field in the grade table as "A"? Maybe this is logical to you. Maybe if the student was present for part of a day, you want to mark him present for purposes of attendance but maybe he left early and so you want to mark him absent for purposes of the exam? I would not allow that conflict to happen without also adding a notes field to the grade table and forcing it to be populated if you have a conflict between the two tables.

NEVER corrupt the meaning of a field by overloading it with some other piece of information. You are already thinking about how you are going to get around the bad data. The solution is easy. Just don't make the data bad to begin with.

NEVER is certainly a strong word especially when capitalized. But you are in control of the schema and it is up to you to ensure that it is sound. When you use one field to have multiple meanings, you end up where we started this post by constantly having to compensate. Why would you ever want to do that when it is so easy to solve this problem correctly and avoid future problems?

PS, I am only replying because you addressed me directly. Otherwise, the person who gave you the bad advice should give you the solution to the bad advice. And then the solution to the solution to the bad advice. We all occasionally give bad or wrong advice but we don't usually double down on it when someone points it out.

I could write for hours on the proper situational use of null as the default for numeric values. Novices have trouble understanding null and compensating when necessary but this particular situation (exam grades) is one where Null is going to save you trouble rather than causing trouble primarily because of the way Access ignores null values when calculating aggregate values which is exactly what you want to happen. You don't need to write functions to ignore zeros so they don't affect grades because some grades will be zero because that is the grade the student earned.

Thank you so much for your concern and guidance. I understand what you want to say. I'll implement proper method for indicating absent and on leave students. Thanks again.