INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query for numbering records from 1 to ....(numeric field) in a table
2

Query for numbering records from 1 to ....(numeric field) in a table

Query for numbering records from 1 to ....(numeric field) in a table

(OP)
I have a table from which I removed some thousands records. Now I want to renumber the records which are still in the table. I have a field for that purpose which is numeric. So first record 1, second : 2; etc until the last record (about 2000 records)
This is not the internal ID, but just a field.

I would like to do that whith a query.

Can anybody show and explain such a query or is this impossible?

RE: Query for numbering records from 1 to ....(numeric field) in a table

Most of the time you would never store that information, but generate it dynamically in a query. If you google "ranking query" you should find many examples. Here is one.
http://bytes.com/topic/access/insights/954764-rank...

What is the purpose of a number? Is it immutable? I assume Access, but other DBs have a built in rank function for sql.

RE: Query for numbering records from 1 to ....(numeric field) in a table

You can't suggest you want to "renumber the records" without also providing the exact expression to use for renumbering. Records are like marble in a box. There is no order so you must be able to provide one based on existing values stored in the records.

Duane
Hook'D on Access
MS Access MVP

RE: Query for numbering records from 1 to ....(numeric field) in a table

(OP)
They are now sorted on that same number, but because I deleted a lot they have numbers starting from 4000.
my application create new numbers based on the recordcount, there starts the problem.

RE: Query for numbering records from 1 to ....(numeric field) in a table

If you are going to do a ranking query on that field, I think you will have to create a new field in order to do an update. I do not think you can update the field you rank on. Once you update the new field, delete the original, and then rename the new to the old.

You can also do this in a recordset if you are better at that. That should also handle cascading updates of child records

CODE

dim rs as dao.recordset
dim i as integer
set rs = currentDB.openRecordset("SomeQuerySortedCorrectly",dbopendynaset)

do while not RS.eof
  I = I + 1
  rs.edit
    rs!sortedField = I
  rs.update
  rs.movenext
loop 

May want to make a copy of your database first, before trying.

RE: Query for numbering records from 1 to ....(numeric field) in a table

If you have this data:

MyTable
MyField  SomethingElse
4000     abc
4001     xyz
4002     klm
4003     iuy
... 

And you want to have this

MyTable
MyField  SomethingElse
1        abc
2        xyz
3        klm
4        iuy
...
 
Why not:
Update MyTable
Set MyField = MyField - 4000

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Query for numbering records from 1 to ....(numeric field) in a table

(OP)
That was the easy solution I was looking for; thanks

RE: Query for numbering records from 1 to ....(numeric field) in a table

Niebotel,

Which response was the easy solution? In addition, it's appropriate to click the "Like this post? Star it!" link to recognize the poster and the answer.

Duane
Hook'D on Access
MS Access MVP

RE: Query for numbering records from 1 to ....(numeric field) in a table

(OP)
OK I dis so now

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close