calculate rank

[Guest]

I have created a table with the following fields

StudentID
Chemistry
Biology
physics
english

The subject fields are numeric for holding the corresponding scores. I would
like to design a report that adds all the 4 scores, averages and add a
position field that shows the position of the student based on the total
score.........i.e. highest total score has 1st position etc. I would also
like to calculate the percentil for each student to display on the report.

Now I have to export the data to excel and perform the calculations before
importing to access. If there is a way the calculation can be done within
Access, I would really appreciate it if someone could help me.

 

[Duane Hookom]

I have seen this type of un-normalized structure quite often in
school/education related applications. Consider changing your structure to
something like

tblStudentClassScores
=================
StudentID
ClassID -> relates to tblClasses.ClassID
Score

You should be able to query for your desired result much more easily with a
normalized structure. If you can't normalize then you can use a union query
to create a normalized view of your table.

 

[Guest]

Thanks Duane. I will make that correction. However, I still face the problem
of calculating the position of the student based on the total score and
his/her percentile score.

 

[Duane Hookom]

You come back with a new table structure and ask about ranking in a report
or query. Some one will help if you provide the proper information.