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."

 

[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