Second maximum value from multiple fields

[buntenix]

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 

[#2]

@buntenix 

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

[#3]

Thanks for your replay isladogs
each subject is absolutely in separate fields in my table.

[#4]

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.

[#5]

Thanks for the advice, lemme try that now.
I really appreciate your consideration.