Line Numbers in a qry

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Is there any simple way to have the a query show also the record number,
e.g. a query returning 3 records could look like

1 Data from 1stRow
2 Data from 2nd row
3 Data from 3rd row

etc

Thanks

DAve
 
Hi,


The data, in tables, has no "fixed" position, it can move from deletion,
insertion, and compaction: when a page of data is full, it is possible that,
under some circumstances, half the data stay on the page and the other half
the data may on another page, as example.

The data, in a recordset, as definitively a position, since you can move
Next and move Previous, on some recordset, but that order is determined at
the moment you open it, through the ORDER BY clause in the SQL statement, or
otherwise.

In a query, you are "between" the two previous states... and there is no
"position" for the record, since the data is only in the table, at that
moment. On the other hand, you can RANK the data, that means you can find
the position of the record "given the position of one of its field among the
other values in the same field"


SELECT a.f1, a.f2, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.f1 >= b.f1
GROUP BY a.f1, a.f2



would rank the records accordingly to their values in field f1.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top