Difficult query

[mwb]

Hello!
I'm rather new here and I have a question that I can't solve.

I have a table with following fields and data:
Startno A B C D E
101 1 2 1 4 4
102 2 3 2 3 3
103 3 1 3 1 1
104 4 4 4 2 2

A, B, C, D E is ranking set by different people
What I want is to count how many people that have set the Start no as
1
1 or 2
1 or 2 or 3
and so on
ex
Startno 1 1-2 1-3 1-4
101 2 3 3 5
102 0 2 5 5
103 3 3 5 5
104 0 2 2 5

I also want to have the sum of the lowest 3, 4 and all 5 values

ex

Startno 3 4 5
101 4 8 12
102 7 10 13
103 3 6 9
104 8 12 16

I know that this is strange but this is the why that we calculate rsults in our sport.

Hope you can help me

 

[Jon K]

I think it's much easier to do it with VBA code.

I have attached a database containing your sample data. When you click on the command button on the form, the results will be calculated and placed in the table "tblResults". The code used is in the On Click event of the command button.
.

 

[Marat]

If you need SQL-query use:
1)

SELECT StartNo,
(SELECT Sum(iif(A=1, 1, 0)+iif(B=1, 1, 0)+iif(C=1, 1, 0)+iif(D=1, 1, 0)+iif(E=1, 1, 0) )  FROM tblSport WHERE StartNo=T_M.StartNo) As [1],
(SELECT Sum(iif(A>=1 and A<=2, 1, 0)+iif(B>=1 and B<=2, 1, 0)+iif(C>=1 and C<=2, 1, 0)+iif(D>=1 and D<=2, 1, 0)+iif(E>=1 and E<=2, 1, 0) )  FROM tblSport WHERE StartNo=T_M.StartNo) As [1-2],
(SELECT Sum(iif(A>=1 and A<=3, 1, 0)+iif(B>=1 and B<=3, 1, 0)+iif(C>=1 and C<=3, 1, 0)+iif(D>=1 and D<=3, 1, 0)+iif(E>=1 and E<=3, 1, 0) )  FROM tblSport WHERE StartNo=T_M.StartNo) As [1-3],
(SELECT Sum(iif(A>=1 and A<=4, 1, 0)+iif(B>=1 and B<=4, 1, 0)+iif(C>=1 and C<=4, 1, 0)+iif(D>=1 and D<=4, 1, 0)+iif(E>=1 and E<=4, 1, 0) )  FROM tblSport WHERE StartNo=T_M.StartNo) As [1-4]
FROM tblSport T_M

2)

SELECT T_M.StartNo, T_Q1.X As [3], T_Q2.X As [4], A+B+C+D+E As [5]
FROM (tblSport T_M 
INNER JOIN
(SELECT StartNo, Min(Sum3) As X FROM
  (SELECT StartNo, A+B+C As Sum3 FROM tblSport
  UNION SELECT StartNo, A+B+D As Sum3 FROM tblSport
  UNION SELECT StartNo, A+B+E As Sum3 FROM tblSport
  UNION SELECT StartNo, B+C+D As Sum3 FROM tblSport
  UNION SELECT StartNo, B+D+E As Sum3 FROM tblSport
  UNION SELECT StartNo, C+D+E As Sum3 FROM tblSport)
GROUP BY StartNo) As T_Q1 
ON T_M.StartNo=T_Q1.StartNo)
INNER JOIN
(SELECT StartNo, Min(Sum4) As X FROM
  (SELECT StartNo, A+B+C+D As Sum4 FROM tblSport
  UNION SELECT StartNo, A+B+D+E As Sum4 FROM tblSport
  UNION SELECT StartNo, A+C+D+E As Sum4 FROM tblSport
  UNION SELECT StartNo, B+C+D+E As Sum4 FROM tblSport)
GROUP BY StartNo) As T_Q2 
ON T_M.StartNo=T_Q2.StartNo

 

[Jon K]

Cool solutions! They run faster than VBA when the table is large.
.

 

[mwb]

Thank you both.
I have missed one thing. In the table i will have several classes where I use a query to select what class to put in result for, how does this affect the code?

Can you also help me how with two more things:

1) If the value in the first count cells is more than three the following cells should be filled with ----- instead of the value, this is for the cells the cells 1, 1-2, 1-3 an so on (or is this easier in the report or in a query)

2) I also want to calculate the results according to the document in the zip file.

Once again thank you very much so far.