How to sort in ascending order in different Query fields (1 Viewer)

[tariqbablu]

CAN ANYONE HELP ME PLEASE???

I am making a STUDENT RESULT DATABASE in Access for different classes. Some class have 7 subjects and some 10 and so on,,,
The rule is as follows:

(1).. Passing Marks 33 (Each subject requires 33 marks out of 100 )

(2)..Total 12 Marks will be given as Grace Marks in any limit of subjects if
needed. But in one subject can be given maximum 8 grace marks out of
12 Grace Marks.
(( That means if any subject < 33 to >24 will get grace marks ))

(3)..Maximum 2 subjects will be allowed for re-exam if <24 OR needed
more than the Grace Marks limit.

In query I made expression for Total of all subjects and Count of total Re-exam subject, working fine as below:

Subjects..........Sub1....Sub2....Sub3....Total.....Tot_Grace.....Re-Exam.....Result
Obt. Marks......25........26.......28........79
Grace Mrks......8..........7.........5.......................20.................??................???

In Re-Exam I gave the following code:

sup: IIf([Quran]<[Gracelimit],1,0)+IIf([Hadees]<[Gracelimit],1,0)+IIf([Urdu]<[Gracelimit],1,0)

But, I don’t know how to do for the GRACE MARKS. Please See 4th student. He needs GRACE MARKS 20 which are more than 12. So after 12 he will be eligible for re-exam that means:

Sub1 = Grace Marks (8)
Sub2 = Re-exam
Sub3 = Re-exam

But if we start to give him Grace Marks like:

Sub3 = Grace Marks (5)
Sub2 = Grace Marks (7)
Sub1 = Re-exam

The only thing I need to start giving the GRACE MARKS from the Subject needed minimum Grace Marks.

As you see above example if we start from the first subject, he needs in (2) subjects re-exam and when we started to give the Grace Marks from the Subject requires minimum Grace Marks effected the result and he needs only one subject re-exam.

Please, help me anyone getting this result, if possible through the MODULE.
Thanks
(Tariq)

 

[MarkK]

It appears as though you might be using Access like it's the same as Excel. What tables do you have? How are they structured?

I'd expect to see a student table, marks table, subject table, and maybe a class table.

 

[tariqbablu]

Lagbolt!
Thanks a lot for your reply.

I want to check the conditions between the subjects range to give them grace marks starting from the lowest required subject.

You will be highly appreciated if you help me getting my target, please,,,

Thanks a lot in advance...

I attached my db here:

 

[tariqbablu]

Dear!

I ahve added classes table also.
There are a total of (18) classes, which have sections too.
If I make a subject table. It would be also too many, as each class has different type and quantity of subjects.
See the attachment.

Thankx,,,,

 

[MarkK]

Here are some changes for you to consider...
Your Main_result table is not the way to go. That is how you would use Excel and NOT how you would use a database.
Cheers,
Mark

 

[tariqbablu]

Thanks dear MARK, I appreciate you..

Let me say that I am new in Access and you are absolutely right that I had been using Excel since long time. And now I decided to work in Access, but really unaware of it.

Please, can you tell me how am I going to get my result as I mentioned before abut the rules. What's the benefit to create Grade Table?

I am sorry to bother you,,,, but still waiting for your kind response, Please...

(Tariq)

 

[MarkK]

In a database table, one record should contain the representation of only one instance of an object. So you have a student table and each row represents one, and only one, student. Then you might have a subject table where each row contains one, and only one, subject. This will not work as a database table...

Subjects..........Sub1....Sub2....Sub3....Total.....Tot_Grace.....Re-Exam.....Result
Obt. Marks......25........26.......28........79
Grace Mrks......8..........7.........5.......................20.................??................???

...because each row contains many discrete objects. That is a good table in Excel, but in a database it is very hard to work with.

I don't exactly know what you are trying to do. It appears that you want to keep track of marks that students received in courses at school. Is that correct?

If so, a single mark must be one item in one row of a table, and it must belong to one student and to one class. So you see my Grades table, it connects one mark to one student and one subject.

Or have I misunderstood your purpose?

 

[tariqbablu]

Thanks again!

I think you understand what I want.
Let me say again that I actually want to make a STUDENT RESULT for a school as donation, because I am making this Free of cost.

So, They have all level of classes:

primary Classes from class (1) to (5)
Secondary Classes from class (6) to (8)
Higher Classes from (9) to (12)
Specialization in classes (13) to (16)

So, we have total of 16 Classes and classes have different type of subjects.

There are 3 rules as follows:
(1) Passing Marks : (33) out of (100)
(2) Grace Marks : (12) in one subject maximum ca be given (8)

Note: a student gets less than 33 will be given grace marks in as many subjects as he needs but in one subject he will be given maximum (8) out of (12) so the rest will be given in other subjects if needed.

(3) If a students gets less than grace range (<25) in two subjects will be eligible for re-exam in those 2 subject and if he gets less than 25 in 3 subjects, will be failed.

Example: (1)
sub1 = 30
sub2 = 32
sub3 = 29
Sub4 = 31
Sub5 = 32

Sub1 requires grace marks 3 (out of 12)
Sub2= 1
Sub3= 4
Sub4= 2
Sub5= 1
So the total grace marks requires (11 out of 12) that means the student is in PASSED category with 11 grace marks.

Example: (2)
sub1 = 25
sub2 = 25
sub3 = 25
Sub4 = 40
Sub5 = 50

Sub1 requires grace marks 8 (out of 12) so the rest are only (4), which cannot be given in sub2 because sub2 needs also 8. So he will be in category for re-exam in Sub2 and Sub3)


Example (3)
sub1 = 65
sub2 = 45
sub3 = 57
Sub4 = 20
Sub5 = 18


You can see we can not apply grace marks here because we can give grace marks only in subjects which are >=25. So the Sub4 and Sub5 are less than grace limit (<25) so the student is eligible for re-exam in these two subjects, but if he has less than 25 in three subjects will be failed.

Example (4)
sub1 = 27
sub2 = 27
sub3 = 65
Sub4 = 20
Sub5 = 18


Sub1 and Sub2 will be applied grace marks (6+6) and Sub4 and Sub5 will be re-examed.

I think now its very clear for you that what is our rule and what we need.


Moreover
The complicated matter is this below one:

Sub1 = 25
Sub2 = 30
Sub3 = 30
Sub4 = 30
Sub5 = 30

See above marks. If we start to give grace marks from sub1 which requires (8) and Sub2 requires (3) So now we have remaining only (1) grace marks which can not be given to the any of rest subjects. So the Student is Failed.

But if we start to give grace marks from the minimum required grace marks, the student will get grace marks like
Sub2=3
Sub3=3
Sub4=3
Sub5=3
Sub1= re-exam

Total grace marks (12) and one subject (Sub1) will be re-examed.
This is exactly what I want to do.
I am sure its not a challenge for you as it is for me. But to say sorry again for your time.
NOTE: I FORGOT TO SAY THAT, ALL SUBJECTS HAVE THREE SEMESTERS.
SEMESTER1 = 20 MARKS
SEMESTER2 = 30 MARKS
SEMESTER3 = 50 MARKS
that's why I made like
1quran....2quran....3quran....Quran....4quran
Quran = (total of all semesters) and 4quran is for Re-Examed marks.
Regards for you,,,

 

[MarkK]

You are worried about grace marks and rules for distributing these too soon. What you need first is a table structure to keep track of your data: students, marks, subjects, classes, semesters. You need to decide how to keep track of that data and get that part of your system working.

If you think of a database like a house, then your tables are your foundation and if you want 1, 2, 3, 4, 5, rooms you need to know that when you build the foundation.

Now, these fancy rules for how to distribute grace marks under what conditions is finishing work like painting and lights. That comes later. When you apply your grace marks, where do you put them? What table?

So back up a little and forget about grace marks for now. You should read up on 'Database Normalization' to understand more about how tables work and about how not to duplicate data.

And then there are decisions to make about what data you need to keep track of. Subject, for instance, presumably Quran is offered as a subject to primary and secondary students. Is Quran always offered at all class levels? Are all courses offered at all class levels? See how it makes a difference to know this? Can a student take Quran at class level 8, and Urdu at class level 9? So is class level determined by subject or by student? See how that matters? Maybe a student at class level 10 always can only take class level 10 subjects.

I suggest that class level is not a thing in itself, but a dimension of something else, either subject or student. So we probably don't need a class table. OK? See how we need to solve these structural problems of defining the objects we are working with?

Cheers,

Later,

 

[tariqbablu]

You are right. and I am sorry for wasting your time.
I told you before I am pretty new in Access.
Anyway I will study more to understand about the Table structure.

May be later I need your e-mail address to contact you again in future.
My e-mail address is tariqbablu_gmail_com

But as you know now about the classes, subjects and students, so you can guide me how many tables I should create and how to make relations between them.

Thanks a lot for your guidance and your time.
Accept my best regards,,,,,

(Tariq)

 

[MarkK]

Tariq, you are most welcome.