Getting the row number from a query

  • Thread starter Thread starter Nicky
  • Start date Start date
N

Nicky

How do I get a row number from a query result? Or short of
that, is it possible to insert a column which will just
count the number of rows that have already been. (i.e.
inserting a column called "row_number")

Thanks in advance.
 
How do I get a row number from a query result? Or short of
that, is it possible to insert a column which will just
count the number of rows that have already been. (i.e.
inserting a column called "row_number")

Thanks in advance.
Click to expand...

It's very easy on a Report - just put a textbox on the report with a
control source of 1 (just the number one) and set its RunningSum
property to Over All.

It's considerably harder in a query. You'll need some field, I'll call
it Sortkey, which is strictly ascending order with no ties; you can
them put in a calculated field

row_number: DCount("*", "[your-query-name]", "[sortkey] <= " &
[sortkey])

This may slow things down quite a bit since you'll need to essentially
run the quiery (using DCount) over and over, once for every row, to
count the records up to the current one.
 
Back
Top