# 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

Sunday, November 1, 2015 2:06 PM

• 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)

• Marked as answer by Tuesday, November 3, 2015 10:58 PM
Tuesday, November 3, 2015 10:56 PM

### All replies

• 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)

• Marked as answer by Sunday, November 1, 2015 4:21 PM
• Unmarked as answer by Tuesday, November 3, 2015 8:03 PM
Sunday, November 1, 2015 4:11 PM
• Worked fantastic!!!!

Thank you

Gary

Sunday, November 1, 2015 4:22 PM
• 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

Tuesday, November 3, 2015 8:06 PM
• 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)

Tuesday, November 3, 2015 8:38 PM
• When I run the new query it now prompts for the student_id

Gary

Tuesday, November 3, 2015 10:20 PM
• 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)

Tuesday, November 3, 2015 10:26 PM
• 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

Tuesday, November 3, 2015 10:34 PM
• link to the doc's for this database

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

Gary

Tuesday, November 3, 2015 10:40 PM
• 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)

• Marked as answer by Tuesday, November 3, 2015 10:58 PM
Tuesday, November 3, 2015 10:56 PM
• Yes that worked.

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

Gary

Tuesday, November 3, 2015 10:58 PM
• 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

• Edited by Tuesday, November 3, 2015 11:19 PM
Tuesday, November 3, 2015 11:18 PM
• 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)

Tuesday, November 3, 2015 11:56 PM
• 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

Wednesday, November 4, 2015 12:01 AM
• 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)

Wednesday, November 4, 2015 12:09 AM
• Interesting idea I will try that out.

Thanks

Gary

Wednesday, November 4, 2015 12:11 AM