incremental counter in query

[BigJimSlade]

Hi, Big Jim here:

I am trying to find a way to create an incremental counter for a query which will be appended to a table. For example:

Rank Name
1 Bob
2 Kevin
3 Mary

Where Name is all of the data I have. I cannot use an autonumber, because I have multiple queries to run and the rank must repeat itself frequently (1 to 10, 1 to 10, etc).

I know I can do this using recordsets, but I was hoping to limit it to perhaps a function in a query.

Thanks in advance,

Big Jim

 

[llkhoutx]

I read an article sometime ago that advised building a function to generate a sequential number in a query. Note that one should include an argument to reset the function.

 

[BigJimSlade]

Hey hout,

I actually have tried a function, but since I am putting it in the SELECT statement, it runs only once as opposed to on each row. Any thoughts on how to create a row-level function?

Thanks again for your help.

Big Jim

 

[llkhoutx]

Query you query adding the function in the 2nd query.

 

[BigJimSlade]

THANKS!

Gracious Big Jim