number each row in query results

[Gez]

Hi,

Is there an expression that can be added to a query to number each row
returned from 1 onwards.

Thanks,

Gez

 

[Sandra Daigle]

You can use a subquery to do this - here's a simple example which includes a
rownumber for a list of customers which are sorted by CustName and using
CustName as the ranking field. Hopefully you can adapt this to your
situation (since you didn't specify how you want to rank). Note that the
subquery can only return one field, in this case it's a count. Also note
that this is not going to be very efficient if your query returns more than
a couple of thousand rows (maybe less if the rest of the query is very
complex).

SELECT
(select
count(*)
from
customers as c2
where
c2.custname < c1.custname) +1 as RowNumber,
c1.Custname,
c1.customerid
FROM
Customers as c1
ORDER BY
by c1.custname;

 

[Stephen Lebans]

See:
http://www.lebans.com/rownumber.htm
Rownumber.zip is a database containing functions for the automatic row
numbering of Forms, SubForms and Queries.

Updated Oct. 13 by Allen Browne. Includes error handling and cleaned
code.


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.