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

[Niebotel]

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?

 

[MajP]

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-ranking-queries-ms-access-sql

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

 

[dhookom]

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

 

[Niebotel]

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.

 

[MajP]

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

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.

 

[Andrzejek]

If you have this data:

[pre]
MyTable
MyField SomethingElse
4000 abc
4001 xyz
4002 klm
4003 iuy
...[/pre]

And you want to have this

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

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.

 

[Niebotel]

That was the easy solution I was looking for; thanks

 

[dhookom]

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

 

[Niebotel]

OK I dis so now