Second maximum value from multiple fields

Copper Contributor

Hello there:

I'm trying to find a way I can find second and third maximum value from multiple fields in a query eg. my data goes like:

name            Math   Biology   Chem   Physics   Max   2Max   3Max

buntenix         80        40           25         12          ?          ?          ?


If anyone out there can help 

4 Replies


The answer may be simple depending on the table structure used.

Is your data normalised?


If this is a crosstab query, you can get the data you want from the original table

However, if each subject is a separate field in your table, it will be much more difficult to do

Thanks for your replay isladogs
each subject is absolutely in separate fields in my table.
That's fine if you are using Excel.
However, its definitely not appropriate for a database.

You should have a at least two tables:
1. tblStudents with fields StudentID, FirstName, LastName etc
2. tblGrades with fields StudentID, Subject, Mark etc

Create a query qryStudentGrades on both tables with StudentID as the join field.
This will be a one-many join i.e. each student will have many subject grades

You will also need to create a totals query to get the Max grade etc for each student
Then a crosstab query is used to display what the data as shown in your original post.

I know it may sound complicated, but it is much easier than what you are trying to do now.
If you continue as you are now, everything you do will be a battle.
Thanks for the advice, lemme try that now.
I really appreciate your consideration.