Finding seven maximum values out of nine

[Gmelsofty]

Hello...
I would wish to find out how i can get only seven maximum fields or two minimum fields in this set of data :
ID Name Subj1 Subj2 Subj3 Subj4 Subj5 Subj6 Subj7 Subj8 Subj9
01 John 78 89 65 45 78 67 45 30 54
02 Mark 90 67 56 45 89 44 99 23 40
The idea here is to find the sum of ONLY seven best performed subjects for each student.
Thanks in advance.

 

[Gmelsofty]

Much thanks.
However I am unable to find out what exactly I wanted.
I kindly request for further explanations.
Thanks.

 

[The_Doc_Man]

Gmelsofty,

When you make the switch from an Excel environment to an Access environment, you run into structural requirements. Excel doesn't have any. In the absence of a cross-cell reference (e.g. =A1+A2, stored in A3), Excel HAS NO STRUCTURE. Every cell is truly independent. You have tools for bulk management and the creation of patterns, but ab initio, Excel is a fill-in-the-blanks grid.

Access, on the other hand, is NOT free-form. It is NOT a spreadsheet. Your problem is that your design was transferred from non-structured environment to a structured one. Therein lies your difficulty. In essence, Uncle Gizmo is telling you (and I am telling you) that your data layout is the very thing that is causing you to have problems.

You need to understand the structural differences. The big one for the problem you have described is that you must learn about database normalization. After normalization of your data, you could write the query VERY QUICKLY in a way that would solve your problem in a heartbeat.

As it is, your layout FORCES you to use tedious and awkward methods. Fix your layout and you'll make your problem easy.