Numbering Records in a query... 1

[Greavesy]

Hi,

I was wondering if anyone has ever put record numbers in a query. I think this will have to be done by calling a function from the query as you cannot add a field to a query which doesn't exist (Like an 'Autonumber' Field).

Any help on this would be great.

Cheers

Mic

 

[AWithers]

I have had a similar problem and got round it by having to insert the data into a temproary table in which one of the fields was an autonumber then selecting from that table

More steps I'm afraid

Andy

 

[Steve101]

You can write a custom function which you can call from the query, but it is quite tricky and not always reliable, as:

(a) You need to use a static variable for counting, which is initialised to zero outside of the query

(b) The function when included in the query has to have at least one input argument associated with a field in the query; otherwise it only gets called once (which is not much good to anyone).

(c) Moving through the query interactively can play merry havoc with the record number displayed, as the function is rerun as you move up or down through the query results.

(d) In short, after experimentation, I gave away this approach as not all that practical; unless you're not using the query interactively, in which case I suppose its OK.

(e) So ... to my knowledge, there is no way to reliably provide a record number identifying the record position of each record returned by a query. It would be a great little feature, but unfortunately so elusive. Andy's method above is basically a way to do what you want, though having to create the temp table is of course undesirable.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au