Forum Discussion
Second maximum value from multiple fields
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 ...
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.
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.
