By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

CrossTab query problem

PhilOfWalton
Expert 100+
P: 1,430
I am involved in organising a fun run for our Rotary Club.

There are 2 main races, the half marathon and the fun run. The latter is broken down into 6 age groups, but for simplicity I am only showing two age groups "Adults" & "Super Veterans"

As each contestant finishes their position is noted regardless of which age group the come in.

The results are as shown in this Crosstab



The aim is to create a query to get first place, second, third etc for each age group (there may well be ties is in this example)

My starting position is to create Query4 on the fly with additional columns to hold those places.



In query view it looks like this ( first few columns only)



What it should look like is this



I have been playing with the function NextPosition for two days and it has got me beat.

Appreciate any thoughts please

Phil
Feb 3 '17 #1

✓ answered by jforbes

Have you considered a Ranking Query?

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
Have you considered a Ranking Query?
Attached Files
File Type: zip RunResults.zip (26.2 KB, 22 views)
Feb 6 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
Thanks, jforbes, extremely helpful.

Slight problem with a tied time that the result sequence goes
1,2,3,5,5,6,7
instead of the more usual
1,2,3,4,4,6,7
but I can live with that

Again many thanks

Phil
Feb 7 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.