Second maximum value from multiple fields

Question

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&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Math&nbsp; &nbsp;Biology&nbsp; &nbsp;Chem&nbsp; &nbsp;Physics&nbsp; &nbsp;Max&nbsp; &nbsp;2Max&nbsp; &nbsp;3Maxbuntenix&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80&nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?&nbsp;If anyone out there can help&nbsp;

isladogs · Answer

buntenix&nbsp;The answer may be simple depending on the table structure used.Is your data normalised?&nbsp;If this is a crosstab query, you can get the data you want from the original tableHowever, if each subject is a separate field in your table, it will be much more difficult to do

buntenix · Answer

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

isladogs · Answer

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 etc2. tblGrades with fields StudentID, Subject, Mark etcCreate 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 gradesYou will also need to create a totals query to get the Max grade etc for each studentThen 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.

buntenix · Answer

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

Forum Discussion

Second maximum value from multiple fields

Share

Resources