To find the first, second, third and fourth highest number (1 Viewer)

[rkisor]

Hi,

I need help in finding the First to fourth highest numbers in four columns.

For Example:

Student Name Subject1 Subject2 Subject3 Subject4
Max 60 40 50 10

I need help in query to find the first, second, third and fourth highest marks in subjects.


Please help.

Kishore

 

[vbaInet]

Your table isn't properly normalised that's why you're having difficulty. Each subject and score should be individual records, not columns. If they were records you'll simply sort by that column.

 

[Uncle Gizmo]

As VB says the design of your database is causing you the issue.

You can read more about it in the following link. There is also a FREE TOOL available in the link to help you transpose your table into the correct format.

http://www.access-programmers.co.uk/forums/showthread.php?p=671226