Row (record) numbering

[BazLondon]

Hi there
I'm sure that this must be easy, so go gently on an old codger!
I have a query for which I wish to add another field containing a running total of the number of records produced by the query (after all sorting etc.).

In other words, if my query produces n records, that new field would contain the value 1 in the first record, 2 in the next and so on to n.

Any ideas would be much appreciated

 

[strep21]

I mostly think an example (attached) says more than a hundred words

 

[BazLondon]

Thanks, guys, for your prompt replies.
Eddie - nice try but I think your solution breaks down if the query contains a sort on other than the primary key.

Pat - amazing isn't it that something that on the face of it looks so easy is in fact quite difficult. I understand your comments re the way recordsets are processed in reports.

My (quite complex) query is the last of a series of queries and forms controlled by a macro. The output from this is Export(ed) and ends up in a table in WORD. I've decided to do the row numbering in WORD afterall - it's quite easy!

Incidentally, I have found a way of achieving the result in ACCESS. It's long winded, but out of interest here goes:-

Change the existing Select query to an Append query which updates a previously emptied "Work_table". This worktable contains two extra fields, Record_ID, a primary key set up as Datatype AutoNumber and a "Row_Number" field set up as Datatype Number.

By this stage, Work_table will contain all the required info in the correct sorted order.

Then, run a subsequent Update query, updating field Row_Number using

[Work_Table]![Record_ID]-DMin("[Record_ID]","Work_Table")+1

Then run a Select Query to provide the means of Export(ing) the info.

It works, but as you suggest, Pat, it will probably run like a (very slow) dog if many records have to be processed.

I think I'll stick to my revised method of doing the biz in WORD!!!

 

[mresann]

This database consists of several queries that autonumber a field based on different criteria. It uses subqueries, not domain functions.

Autonumbering Example (In Alternating Color Tutorial)