Increment a number using Select

Question

How would I increment a number using this query:

SELECT [%$##@_Alias].student, Max([%$##@_Alias].TheJump) AS MaxJump
FROM (SELECT student,totalin1 AS TheJump
   FROM Totalin
   UNION 
   SELECT student,totalin2 AS TheJump
   FROM Totalin
   UNION
   SELECT student,totalin3 AS TheJump
   FROM Totalin
)  AS [%$##@_Alias]
GROUP BY [%$##@_Alias].student
ORDER BY Max([%$##@_Alias].TheJump) DESC;

I want to add a filed call place starting with 1 then increment it by 1

I got the original query off here by can't find it now.

---

Gary

Answer 1

Do you want to compute the rank? If so:

SELECT B.student, B.MaxJump, 
  (SELECT Count(*) 
  FROM 
    (SELECT A.student, Val(Nz(Max(A.TheJump),0)) AS MaxJump
    FROM 
      (SELECT student,totalin1 AS TheJump
      FROM Totalin
      UNION 
      SELECT student,totalin2 AS TheJump
      FROM Totalin
      UNION
      SELECT student,totalin3 AS TheJump
      FROM Totalin
       ) AS A
      GROUP BY A.student
    ) AS C
    WHERE C.MaxJump>B.MaxJump)+1 AS Rank
  FROM 
    (SELECT A.student, Val(Nz(Max(A.TheJump),0)) AS MaxJump
    FROM 
      (SELECT student,totalin1 AS TheJump
      FROM Totalin
      UNION 
      SELECT student,totalin2 AS TheJump
      FROM Totalin
      UNION
      SELECT student,totalin3 AS TheJump
      FROM Totalin
       ) AS A
  GROUP BY A.student
  ) AS B;

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Worked fantastic!!!!

Thank you

 

---

Gary

Answer 3

How can I include student_id from the jump table? I need so I can run an edit table using the table that query makes.

Thanks

---

Gary

Answer 4

Just add student_id wherever you use student:

SELECT B.student_ID, B.student, B.MaxJump, 
  (SELECT Count(*) 
  FROM 
    (SELECT A.student_ID, A.student, Val(Nz(Max(A.TheJump),0)) AS MaxJump
    FROM 
      (SELECT student_ID, student,totalin1 AS TheJump
      FROM Totalin
      UNION 
      SELECT student_ID, student,totalin2 AS TheJump
      FROM Totalin
      UNION
      SELECT student_ID, student,totalin3 AS TheJump
      FROM Totalin
       ) AS A
      GROUP BY A.student_ID, A.student
    ) AS C
    WHERE C.MaxJump>B.MaxJump)+1 AS Rank
  FROM 
    (SELECT A.student_ID, A.student, Val(Nz(Max(A.TheJump),0)) AS MaxJump
    FROM 
      (SELECT student_ID, student,totalin1 AS TheJump
      FROM Totalin
      UNION 
      SELECT student_ID, student,totalin2 AS TheJump
      FROM Totalin
      UNION
      SELECT student_ID, student,totalin3 AS TheJump
      FROM Totalin
       ) AS A
  GROUP BY A.student_ID, A.student
  ) AS B;

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 5

When I run the new query it now prompts for the student_id

---

Gary

Answer 6

Oops. Try this instead (WARNING: it's just a guess since I don't know the structure of your tables):

SELECT Jump.Student_id, B.student, B.MaxJump,
  (SELECT Count(*)
  FROM
    (SELECT A.student, Val(Nz(Max(A.TheJump),0)) AS MaxJump
    FROM
      (SELECT student, totalin1 AS TheJump
      FROM Totalin
      UNION
      SELECT student, totalin2 AS TheJump
      FROM Totalin
      UNION
      SELECT student, totalin3 AS TheJump
      FROM Totalin ) AS A
      GROUP BY A.student
    ) AS C
    WHERE C.MaxJump>B.MaxJump)+1 AS Rank
  FROM
    (SELECT A.student, Val(Nz(Max(A.TheJump),0)) AS MaxJump
    FROM
      (SELECT student, totalin1 AS TheJump
      FROM Totalin
      UNION
      SELECT student, totalin2 AS TheJump
      FROM Totalin
      UNION
      SELECT student, totalin3 AS TheJump
      FROM Totalin
       ) AS A
  GROUP BY A.student
  ) AS B INNER JOIN Jump ON B.student = Jump.student

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 7

Did not work here are the tables.

Jump Table
ID AutoNumber
student_id Number
dis_ft_1 Number
dis_in_1 Number
dis_ft_2 Number
dis_in_2 Number
dis_ft_3 Number
dis_in_3 Number
dis_ft_4 Number
dis_in_4 Number
dis_ft_5 Number
dis_in_5 Number
dis_ft_6 Number
dis_in_6 Number

Table Student
ID AutoNuber
Student Short Text
School Number
InMeet  Yes/No 

---

Gary

Answer 8

link to the doc's for this database

https://www.dropbox.com/s/91nhpb8i8q76utw/doc_rptObjects.pdf?dl=0

---

Gary

Answer 9

That's a LOT of information to wade through...

Does this work?

SELECT Student.ID, B.student, B.MaxJump,
   (SELECT Count(*)
   FROM
     (SELECT A.student, Val(Nz(Max(A.TheJump),0)) AS MaxJump
     FROM
       (SELECT student, totalin1 AS TheJump
       FROM Totalin
       UNION
       SELECT student, totalin2 AS TheJump
       FROM Totalin
       UNION
       SELECT student, totalin3 AS TheJump
       FROM Totalin ) AS A
       GROUP BY A.student
     ) AS C
     WHERE C.MaxJump>B.MaxJump)+1 AS Rank
   FROM
     (SELECT A.student, Val(Nz(Max(A.TheJump),0)) AS MaxJump
     FROM
       (SELECT student, totalin1 AS TheJump
       FROM Totalin
       UNION
       SELECT student, totalin2 AS TheJump
       FROM Totalin
       UNION
       SELECT student, totalin3 AS TheJump
       FROM Totalin
        ) AS A
   GROUP BY A.student
   ) AS B INNER JOIN Student ON B.student = Student.student

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 10

Yes that worked.

Thank you soooo much for helping me. Hard to kind people like you that like to help others.

---

Gary

Answer 11

One last question :-) When I run this query I can't edit the dist fields or any fields. What do I need to do to be able to edit the fields.

---

Gary

Answer 12

You can't - records in totals queries (involving GROUP BY) cannot be edited, since they present an aggregated view over multiple records. Union queries aren't editable either.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 13

Will that is not good.

After 3 jumps of all the students I have to find the top 6. Then those 6 students jump 3 more times then I rank all students from longest to smallest jump.

Much harder to do than I thought.

Again thank you for all your help. I will try and figure out another way.

---

Gary

Answer 14

You could turn the query into a make-table query to produce a temporary table with the intermediate results. Each time new results are in, you can run the make-table query again and overwrite the temporary table.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 15

Interesting idea I will try that out.

Thanks

---

Gary