row counting or index query

  • Thread starter Thread starter msj120
  • Start date Start date
M

msj120

here is my little problem:

i am trying to design a query where the results set contains an
auto-numbered column (a kind of index or running total of the number of
rows returned). this seems like a simple function, but my search for
this answer has led me here. please help

thanks
MJ
 
Dear MJ:

It is possible to "rank" all the rows in a query and get a column with
a unique integer for every row in many cases.

In order to do so, you must have a set of columns that form a unique
key to the rows in the query. The rows will be ranked in the order of
these columns.

This is a "transitory" thing. If the values in the unique key change,
or if rows are inserted or deleted, the numbering will change the next
time the query is run. However, there will not be gaps in the
numbering (again assuming the key is unique).

On the other hand, if you assign a sequential numbering of the rows in
a permanent way (meaning you must record these value in the table)
then there will be gaps where rows have been deleted. Having a
permanent identity to these numbers and having no gaps are not
compatible requirements.

If you will post a query that shows you the columns you want to see,
and if you will specify the ordered set of columns that make up the
unique key by which you want the rows numbered, I'll modify that to
add a "ranking" for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top