Relative Record Number

[Lord Bonez]

Hello all,

I have the following issue:

I have a table with 150000 records like this:

ID# PostalSoringCode
------------------------------------------------------------------------
2 0001/1000AA/48/B000001/S001/101
1 0001/1000AA/48/B000001/S002/101
3 0001/1000AA/48/B000001/S003/101
5 0001/1000AA/48/B000001/S004/101
4 0001/1000AA/48/B000001/S005/101

The PostalSoringCode is in the right order, so that one may not change. I want to renumber the ID#, starting from 1 to 150000. In for example DB2 there is something like Relative Record Number (RRN). There it would be possible to do UPDATE TABLE SET ID# = RRN(ID#).

Does anyone have a CLUE how to do this in access?

thanks

 

[Dwight]

Check out the post "Increment a Field, DMAX."

 

[Pat Hartman]

Why is it important to you to renumber existing records? You have two unique ids already in these records including one you can sort on.

The easiest way to accomplish the renumbering (hope you don't have any related tables) is to create a new empty table with an autonumber. Then create an append query that selects the rows from the old table and sorts them by postal code. The records will be appended in postal code order so the autonumber will be sequential.

 

[Lord Bonez]

Hello

I have to renumber because the printer demands a record number in the right sequence, otherwise their machines can not print the magazines.

I tried the solution you are suggesting here, but Access is apparently not built to run queries with 100000+ record databases. (originally this table comes from the AS/400, but I have to make some formatting modifications in access)

 

[Rich]

but Access is apparently not built to run queries with 100000+ record databases

what do you mean by that?
I have a query with 1.5 million records and others posting here have even more. What problem do you have?

 

[Lord Bonez]

for example, what I tried to do to solve this, I changed the format of the first field from Number to Autonumber. When I save that, the program runs out of memory while updating the DB
(while I have 256 MB)
But I will try to create a table and then insert it. it might work..tnx