Row Number

  • Thread starter Thread starter jjh
  • Start date Start date
J

jjh

I simply want a query that will return the actual row
number so I can display the row number in a listbox to
show the ranking of staff seniority.

The query is sorted by staff_start_date, so it will be in
the proper orer, but I can't fugure out how to show the
relative position this person is in.

So, it would be something like this:

Select (rownumber or whatever it should be), staff_name,
staff_start_date from tbl_staff order by staff_start_date;

Thanks, Jeff
 
Hi,

There is no row number in a table. In a recordset, where the data is
"fixed" in its relation through other records, for the family-photo, you
have a record number, but you have to build the recordset first, from data
in the table ... and the table does not supply a row number... (unless you
coerce it, such as appending data in one batch, and the table has an
autonumber). It is not a lost cause, on the other hand, if you want to RANK
the data, such a counting the number of values that are less or equal to the
one of the actual "row":


SELECT a.*, (SELECT COUNT(*)
FROM myTable As b
WHERE b.something <= a.something ) As Rank
FROM myTable As a
ORDER BY a.something


replace the something with the real field that define the "ORDER BY". Note
that I assume there is no duplicated value for values in that field.

Hoping it may help,
Vanderghast, Access MVP
 
Back
Top