Row (record) numbering (1 Viewer)

[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

 

[Pat Hartman]

It is really much more efficient to do this with a report if that is the ultimate destination of the data. Calculating running totals in a query is extremely inefficient and you will notice slow performance if you have more than a few hundred rows in the recordset. The report doesn't have this problem due to the difference in the way the recordsets are processed.

 

[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!!!

 

[Pat Hartman]

If you are making a temporary table anyway, sort the data and use a make table query. Then add an autonumber column. This method, the Access report method, and the word table method all process the recordset ONCE in order and so the generation of the number is as efficient as can be. The query method as you see requires the use of domain functions. Each domain function runs its own query. So if you have 100 rows, you run 100 queries and that means you'r processing 100 x 100 or 10,000 records and that's for just a 100 row table!

 

[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)