Computing Students Total Points (1 Viewer)

[Gmelsofty]

Hello!
I have a students table with the following details:
Reg no, Name, Subject, Category, Points
1324, Silas, English, Compulsory, 10
1324, Silas, Kiswahili, Compulsory, 11
1324, Silas, Maths, Compulsory, 12
1324, Silas, Biology, Science, 9
1324, Silas, Chemistry, Science, 10
1324, Silas, Physics, Science, 9
1324, Silas, History, Humanity, 8
1324, Silas, Geography, Humanity, 11
1324, Silas, Computer studies, Applied, 10

- My aim is to compute student total points based on the following criteria:
- Points of all compulsory subjects are included in the total then STRICTLY four best points
of the remaining subjects are added to the compulsory ones.
- For example the above data should yield the following:
- {10+11+12} + {11+10+10+9} = 73 points.

- The output is based on a cross tab query which generates a list of all the student.
- Any help will be appreciated.
- Thanks in advance.

 

[isladogs]

I don't know if you are also UK based but this looks very much like the EBacc calculation to me

If so I have a lengthy routine to calculate both EBacc & Progress8.

If you don't get a simple answer to this question elsewhere, I may be able to assist

 

[isladogs]

To get you started try this:

1. Create an aggregate query A with filter category = 'compulsory' & get the total points for these - lets call it TPCS

2. Create a 2nd query B with filter category <> 'compulsory' and sort by points descending.

3. Create another aggregate query C based on the previous one.
Filter for Top 4 results for each student
I'll leave you to think how that should be done!
Get the total points for these - lets call that TOP4

4. Create a final query based on A & C and linked by RegNo
Add a field TotalPoints = TPCS + TOP4

I'm sure this could be done more concisely but doing it this way makes the logic hopefully easy to follow

 

[Gmelsofty]

I like your idea ... but now in case i sort the points in a descending order how shall i evade the issue of say tying points which exceeds top 4 or maybe to be more concise how shall i acquire the top 4 points. Thanks.

 

[isladogs]

I like your idea ... but now in case i sort the points in a descending order how shall i evade the issue of say tying points which exceeds top 4 or maybe to be more concise how shall i acquire the top 4 points. Thanks.

And I thought I was leaving you to think how it could be done !!!
I'm not going to tell you exactly how to do that as you'll get more satisfaction from working it out
If you need help, try Allen Browne's website: http://allenbrowne.com/tips.html

but here's a bit more help....
a) Sort in descending order - it doesn't matter if there are several tying values - you just need the top 4
b) Create a TEMP table with the top 4 values for each student
c) Get the total points for these - TOP4

etc ...

NOTE: You can delete the TEMP table when done

 

[Gmelsofty]

Thanks for your brilliant ideas.... however av struggled a lot to no yield.... any other suggestion please if you don't mind.

 

[isladogs]

struggled a lot to no yield

That doesn't tell us anything useful

What exactly have you tried and what were the results?

I supplied a website to look at ... and in a PM said to look at subqueries
Have you done either of those?

 

[Gmelsofty]

Thanks. Let me revisit it once again.