Get Index

[Middlemarch]

I have some ID strings in a table as below and what to know the index for 'Next'.


For example if 43094048 I want to return 3, 41911200 1 etc.


I can think of some cludgy ways to do this but I suspect there's a clever 'Access" method and wonder what it might be?
Thanks


First Next
42607288 41911200
42607288 35415599
42607288 43094048

[June7]

Sorry, don't understand. Need to better explain the rules for this algorithm. Why would you want to return 3 and 41911200?

[Middlemarch]

Sorry , typo should have been 43094048, It would be 3 as its the 3rd item (and 41911200 is the first).
And the Order By field is "num"

[June7]

Ooops, I see that number now. But still not entirely clear what you want. If you want to calculate a row number for each group in FIRST field, that is not simple in Access query.

Review http://allenbrowne.com/ranking.html

and https://stackoverflow.com/questions/...nter-per-group

and https://stackoverflow.com/questions/...umber-sequence