Custom autonumbering field

[pcrosby78]

One more question today for you guys,

Is there an easy way to create a numbering (autonumbering) field to insert numbers automatically that aren't a primary key.

For example i want it to have a custom numbering sequence of like 1,2,3,4,1,2,3,4...etc.

The reason i need to do this so that the mailing list i work on i can assign a "rotation" number to the names. if i have 100,000 names and can only mail out 25,000 a month i would like to assign the rotation to start over after ever 4 names.

Because of this if the list grows to 200,000 i would need to have a way to update the "rotation number" easily to say every 8.

Any ideas?

I am a novice at this so please explain clearly as if to someone that has minimum knowledge of access (that's me) but i can somewhat code in VB if that helps

pcrosby78

 

[mikey69]

Best to use Visual Basic. Create a table with a single field and one record which is a counter. Read the value in each time you create a new entry, then add 1 to the value, test and reset to 1 if it has become larger than 4 or 8 or whatever cycle you decide on. Update the record and close the table.

The code will lok something like
Dim DB as database
Dim RS as recordset

Set DB = currentdb()
Set RS = db.openrecordset
Yourfield = RS.Fields(1)
RS.Fields(1) = RS>Fields(1) + 1
If RS.Fields(1) > CycleValue
RS.Fields(1) = RS.Fields(1) - CycleValue
End If
Rs.update
RS.Close

 

[pcrosby78]

sounds good mikey, but i am a novice at access, (usually using excell with VB) so how would i enter the code into the table & what is the control variable that i would assign the maximum number to cycle to...

Is it possible to have the table you talk about to have two fields, one containing the code and the other containing a field that i can input a maximum rotation number in, so say if i entered 8 instead of 4, then it would go through and totally update the other mailing list tables to have a rotation 1-8, instead of 1-4?

any more advice is greatly appreciated.

-pcrosby78

 

[GeekinGlasses]

You are making this more complicated than it needs to be. You can run a query on the table (and assuming you have an autonumber field on the table). In a new column in the query add:
Expr1: Int([mailing listid]/2)
and in the criteria of the new column add:
([mailing listid]/2)

explaination:
mailing listid is the field of the autonumber
2 can be changed to whatever number of records you want to skip.
basically you are asking the query to look for any autonumber divided 2 and return the whole number of it (using the Int function). Then if the whole number is the same as non divided whole number, then show it.
simply put the vba code would be :
if int([mailing listid]/2) = [mailing listid]/2 then...
You are asking it to divide the autonumber by 2 and return only the number without remainders

if you had 4 records
mailingautonumber int(Mailing/2) Mailing/2
1 0 .5
2 1 1
3 1 1.5
4 2 2
since 2 and 4 have equal values they are returned

 

[GeekinGlasses]

I forgot to add. My way is better because it elimnates unneeded fields in your database (which is just good database design) If you really want to get creative, instead of hardcoding the 2 (or whatever number you want) in the query, simply put a blank query column in with title Number to Step, then every time the query is run it will ask for "Number to Step" value to be input; and put [Number to Step] in place of the 2 in the other query column.

I hope i explained this well enuf, and not confused anyone.