Renumbering a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to run a query which could "re-number" a field sequentially?
(1...2...3...)

thanks in advance
 
Dear David:

I'm going to assume that you want to number the rows of your query
(not the fields) by adding a column to the query.

This is commonly called "Ranking" the query. It would commonly be
based on some key to the query (a set of one or more columns that sort
the rows). If this key is unique, then every Rank value derived from
it will be unique. If this key is not unique, there will be ties,
wich will be appropriately reflected in the ranking.

Do you want to rank every row in the query starting from 0 or 1? Do
you want the rank to start over based on some column of set of
columns?

If you'll provide the SQL to a query that displays what you want other
than the rank column, I'll add that and send it back. You must answer
the above questions for me to be able to do this.

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

Thanks for your response. Actually, I'd like to replace a NUMBER value
currently in the field with a new number..one that increments by one with
each subsequent record. Is this more clear?

-David
 
Dear David:

Once you have this working as a query, changing it to update an
existing column is a simple derivative of that query.

Now, the column you want to replace: is it a primary key, or a unique
index to the table? If there are such constraints on the column, you
will probably have to remove them temporarily before you can update
the column. In addition, if there are any relationships to other
tables involving this column, then the situation is much more complex.

To get my help, please continue with the questions I asked before.

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

I don't believe there are currently any such constraints. After these new
numbers are added, I'd like to change the field to be a primary key. As of
now, no other table in the db is related to this particular field.

-David
 
Dear David:

Please respond to the questions I asked initially.

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