Member Avatar for Zacharia

Hope you are doing okay today!

I am using ms access 2010. I want to calculate the students points and division for each student. Students are taking 9 subjects and I want to take only 7 (the highest score). The grades are: 75-100=A, 60-74=B+, 50-59=B, 40-49=C, 30-39=D, 20-29=E, 0-19=F. The points are:A=1, B+=2, B=3, C=4, D=5, E=6, F=7. I have managed to convert Scores into point by using the IIF() function, my problem is how can I obtain the sum of only 7 subjects out of 9 (only those with smallest points ignoring the other 2)?

Please help

  • 2 Contributors
  • 1 Reply
  • 136 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by rch1231
Member Avatar for rch1231

Hello,

You would use a desending sort and the LIMIT function to limit your output to the first 7 rows.

select student_id, grade 
from grades 
order by grades desc
limit 7

wrap that inside a query that goes through each student by id and you will have your results.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.