Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I don't need to reset the AutoNumber value like this question, but rather would like to first set the number to:

1001

Which then increments like normal:

1001

1002

1003

...

How do I go about doing this in Access 2010?

share|improve this question
5  
Not an answer, but a warning: Autonumber is not designed to be managed in this way. I've seen countless people try it. It never works because the DB will do weird things incrementing sometimes (failed writes still increment the number, for example). Autonumber does not guarantee that the record numbers will be consecutive. Indeed, in rare cases it won't even guarantee the number will be unique. It's very useful for creating a primary key for a small table. It's a little risky for large tables. It's absolutely awful for using as an actual data field. He –  Bacon Bits May 24 '11 at 20:42
    
@BaconBits... what then is AutoNumber meant to be used for? –  KronoS May 24 '11 at 20:59
    
Rest of @BaconBits's comment: nce you see articles like this when something goes pear-shaped because people expect Autonumber to be something it's not. If you need to generate numbers in this manner, write the queries and such in the DB to do it properly yourself. –  BloodPhilia May 24 '11 at 20:59
add comment

2 Answers

up vote 5 down vote accepted

Although Access doesn't offer this function natively, it can be achieved through a query, like the following:

CREATE TABLE TableThatIncrements
(
Id AUTOINCREMENT(1001,1)
)

This will create a single table that's called "TableThatIncrements" with a single column, named "Id". You can now use the table editor to add properties and/or other columns.

Change

AUTOINCREMENT(x,y)

to suit your needs, where x is the initial increment number and y is the number to increment with. So AUTOINCREMENT(100,5) will yield: 100, 105, 110, 115, etc.

If you want to alter an existing table, use the following query. Make sure that specific table's tab is closed so Access can lock it down and alter it.

ALTER TABLE TableThatIncrements
   ALTER COLUMN Id AUTOINCREMENT(1001,1)

You can run a query in Access by doing the following:

  1. Go to the "Create" tab and click "Query Design"
    enter image description here

  2. Just close the window that appears which asks you to select tables, we don't need that.

  3. Go to the "Design" tab and click the button with the arrow until you get a textual input screen. (By default, it says SELECT;).
    enter image description here

  4. Delete the default text and paste the above query.

  5. Click "Run".
    enter image description here

share|improve this answer
    
good answer, but that creates a new table... or am I wrong? Can this be done within an already created table? –  KronoS May 24 '11 at 20:42
    
@KronoS Updated my answer –  BloodPhilia May 24 '11 at 20:48
add comment

You have to trick Access because it assumes you're not interested in an autonumber different than the one it provides.

You'll need to start the table out with the custom autonumber, which will require some setup. But once it's going you can just continue adding from there.

Google "access change autonumber start" and you'll find several options for doing this. Most rely on creating an append query to modify the field.

Create the first table that contains the counter type field that you want to start on another number. Don't enter any records.Create a second table, with a single long integer number type field that has the same name as the counter field in the first table. Create a record in the second table by entering a number one less than the number you want to start the counter at in the first table. Create an append query, to append the one record in the second table to the first table, and run it Delete the second table, delete the record that you appended to the first table, and start entering data.

From MVPS.org: http://access.mvps.org/access/tables/tbl0005.htm

If you don't feel like building an append query, you could always set up the table and copy and paste 1000 lines of dummy data from excel, delete the records until you get to number 1001, and go on from there.

UPDATE: Adding explanation for reason

The reason behind autonumber being only set to start from 1 is that the autonumber field is meant to be used as an internal reference field, and in good database practice, should not generally be used in any publicly visible form. It is used in cases where there is not already a unique item in a given table to create a unique entry by which the table can be searched and indexed.

share|improve this answer
    
There's gotta be an easier way to doing this.... –  KronoS May 24 '11 at 20:20
    
I suppose the good news is that if you need to create several tables with such an autonumber, you can simply create one, save it in a "template" database file, and just copy it to new database files as you need it. –  music2myear May 24 '11 at 20:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.