running total of records in query

[alecgreen]

Hi

I want to show a running total of the records in my query. By this I
mean, if I run my query and 5 records are displayed I get 1,2,3,4 and
5 (in a calculated field in the query) against each record. If my
query shows 9 records I get 1,2,3,4,5,6,7,8 and 9 and so on. I been
trying to do this for days now!

Many Thanks

Alec

 

[Bob Barrows]

Hi

I want to show a running total of the records in my query. By this I
mean, if I run my query and 5 records are displayed I get 1,2,3,4 and
5 (in a calculated field in the query) against each record. If my
query shows 9 records I get 1,2,3,4,5,6,7,8 and 9 and so on. I been
trying to do this for days now!

Stock answer: why do it in the query? Do it in a report.

Longer answer: if your results can be ordered without ties, a subquery can
be created to count the records whose values in the ordering fields are less
than or equal to the values in the current record. More details are required
if you need a more specific answer.
Alternatively, create a table with an autonumber column and insert the
results into it. Then select the numbered results from the table. This will
require VBA code to automate it, since the table has to be created each
time. If you do take this route, you should prevent database bloat by using
VBA to create a temporary database file in which the table with the
autonumber field is created. Delete the database file after selecting the
records from it.