MS Access - Identify highest 3 values across columns -

-

i have table containing data following:

id    team1    team2    team3    team4    team5    team6 1     5        0        1        2        3        1 3     1        4        0        2        4        1 5     0        0        4        2        1        1 6     4        1        1        2        0        0

i want derive 3 new columns data 1st, 2nd , 3rd highest values across columns, so:

id    team1    team2    team3    team4    team5    team6   top1  top2   top3 1     5        0        1        2        3        1       5      3      2 3     1        4        0        2        4        1       4      4      2 5     0        0        4        2        1        1       4      2      1 6     4        1        1        2        0        0       4      2      1

is possible in access? want rank them equal values ranked incrementally, example:

id     value   rank 3      4       1 3      4       2 3      2       3 3      1       4 3      1       5

i'm open using vba if there way achieve through means. data available unpivoted if it's easier pull out top 3 values in format.

sample of unpivoted data:

id    team    score 1     team1   5 1     team3   1 1     team4   2 1     team5   3 1     team6   1 3     team1   5 3     team2   4 3     team4   2 3     team5   4 3     team1   1

take function (from finding max of 3 inputs vba)

sub maxthree()   'calculates maximum of 3 numbers'   dim x, y, z single   x = inputbox("enter first number!")   y = inputbox("enter second number!")   z = inputbox("enter third number!")   msgbox ("x: " & x & " y: " & y & " z: " & z)   if x > y       if x > z           msgbox ("the maximum : " & x)       else         msgbox ("the maximum : " & z)       end if   else       if y > z           msgbox ("the maximum : " & y)       else           msgbox ("the maximum : " & z)       end if   end if   end sub

adjust return highest value (rather display in msgbox). construct 2 additional functions second , third value.

use function in sql query columns want,

good luck.


Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5); ...
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:...
Read more