I would like to know how to generate a series of number increasing by one

[RobV]

I need to find a way of generating an incremental number in a series of fields on an existing database. There will be two series one generating a number from 1 to 49999 and a secon series generating a number from 50000 to 99999. I have only basic knowlege of Access so help is definately needed.

[joelmeaders]

Need more information.

Is this all going to happen in a table or multiple tables?
Are all numbers permanently tied to a record?
Is this a one time event or will it happen multiple times?

[ssanfu]

Maybe this will help you:

http://www.rogersaccesslibrary.com/forum/topic395.html

or

http://www.baldyweb.com/CustomAutonumber.htm

[RobV]

The database is currently used for booking in work and has all the usual fields. We now have to include a unique reference number which consists of two five digits fields The first one is easy generated from a drop-down field. The second one is the 'difficult' one in that it must be a unique number between 00001 and 49999 and at another geographical location on a separate database from 50000 to 999999. Both fields will then provide a unique reference number for each job. Hope this makes sense and thanks for your time. Rob

[rpeare]

Can you give an example of the FULL field as you want it to appear. I'm getting a bit lost by your description is it a 3 part number that you want for your end result i.e.

[CustomerNumberFromDropDownBox]-[Incrementing Number Between 1 and 49999]-[Lookup Value from Geo Coded Table]

i.e.
1-00001-50000
1-00002-50000
1-00003-50000
1-00001-60000
1-00001-60000
2-00001-50000
2-00002-50000
2-00003-50000

where the 'counter' is reset for each customer for each geo code

Or is it strictly

[Incrementing Number Between 1 and 49999]-[Lookup Value from Geo Coded Table]

i.e.
000001-50000
000002-50000
000003-50000
000001-60000
000002-60000

again assuming you want to restart the count portion of the filed based on the geo code.

How are you looking up your geo code? are you choosing it from a combo box or are you retrieving a value from a table based on some other criteria on your data entry form?

[bcmarshall]

I'm not sure if this will help or not. I have a routine that will create a million records in a few seconds. You can simply limit it in the criteria expression to whatever range you want.

You can create a table [tbl09] with a single numeric field [num] and 10 records with values 0 - 9.
Add this one table into a query many times to create a SQL statement like following to create 1,000,000 records


SELECT [tbl09].[Num]+([tbl09_1].[Num]*10)+([tbl09_2].[Num]*100)+([tbl09_3].[Num]*1000)+([tbl09_4].[Num]*10000)+([tbl09_5].[Num]*100000) AS Numbers FROM tbl09, tbl09 AS tbl09_1, tbl09 AS tbl09_2, tbl09 AS tbl09_3, tbl09 AS tbl09_4, tbl09 AS tbl09_5 ORDER BY [tbl09].[Num]+([tbl09_1].[Num]*10)+([tbl09_2].[Num]*100)+([tbl09_3].[Num]*1000)+([tbl09_4].[Num]*10000)+([tbl09_5].[Num]*100000);

Just paste the SQL into a blank query after creating the table, and then limit the output in the criteria.

[rpeare]

I don't think he's after creating a bunch of records at once, only when a new project is started. We'll have to wait for a response.

[bcmarshall]

It was a little hard to tell. If he wanted to create them one at a time he could use autonumber or DMax +1, so maybe I missed his purpose. The routine I offered works great for creating the sequentially numbered records. He could create them and append them, also. It was just a shot in the dark, but it's very handy if you ever need to create a whole bunch of records!