adding a recurring number field to a table

[macdan82]

Hello All, your collective wisdom would be greatly appreciated to assist me in overcoming this..

I have an address table that contains >400,000 records, there is an autogenerated primary key in place but i dont wish to use this for a sort, instead I would like to add a new field that (from top to bottom) would add 1,2,3...50 then start back at 1 again and repeat for each record in sequence.

I have looked at adding a new field via table design thinking that autonumber might hold the key but i think i was getting a bit ahead of myself...

Is there a way to acheive this by perhaps an append query or with brute force and ignorance might auto number work in the fashion i'm hoping for?

Any suggestions would be greatly appreciated

 

[macdan82]

further to this, whilst creating this field in the main table would be preferable, there is a query in place that pulls records based on criteria, if it was possible to create an extra field within the query that would create the recurring numbers and allow me to sort on that this would also work, but again, havent a clue as to how this might be acheived.
Again, many thanks in advance

 

[sparks80]

Lets say the Autonumber primary key in your table is called ID.
To create a query with the recurring number you want you can generate a new field based on the autonumber like this:

RecurringNumber: (([ID]-1) Mod 50) + 1

You can either sort by this field in the query, or add it to a new field in your table with an update query:

UPDATE YourTableName SET YourTableName.YourNewField = (([ID]-1) Mod 50)+1;

The recurring number will always be the same in relation to the ID field, so will not change if the order of the records changes.

 

[macdan82]

sparks80 - thats brilliant - thankyou!