starting value for autonumber in access 2007

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I need help adding a table. But I am being asked to use autonumber for the
key but they want the key to start at 100 and increase by 5 for each new
record. Please note I am a novice and don't have programming experience. I
was told that in prior version of access I could specify the start number.
How can I do this in access 2007
 
The Access interface doesn't do this for you. You will need to set the Seed
and Increment properties of the column programmatically.

Simplest way is to:
1. Create a new query.

2. In first dialog, choose Design view.

3. Cancel the second dialog (Add Table.)

4. Switch to SQL View (left of ribbon.)

5. Paste this in:
ALTER TABLE [Table1] ALTER COLUMN [ID] COUNTER(100, 5);

6. Substitute your table name for Table1, and the name of your autonumber
field for ID.

7. Run the query.

No need to save the query.
 
Richard,
Allen's answer is correct but be aware that, as you are using an Autonumber
field you are not guaranteed to have all the records in the table
sequentially numbered with an interval of 5.

For example, if someone starts to enter a record but then cancels the entry
the id for that record is "lost". If someone then enters a record there
will be an interval of 10 between that record and the previous record
entered.

You need to point this out to the person requiring the work before handso
that they are aware that there is a limitation to the method.

--
Terry Kreft


Allen Browne said:
The Access interface doesn't do this for you. You will need to set the
Seed and Increment properties of the column programmatically.

Simplest way is to:
1. Create a new query.

2. In first dialog, choose Design view.

3. Cancel the second dialog (Add Table.)

4. Switch to SQL View (left of ribbon.)

5. Paste this in:
ALTER TABLE [Table1] ALTER COLUMN [ID] COUNTER(100, 5);

6. Substitute your table name for Table1, and the name of your autonumber
field for ID.

7. Run the query.

No need to save the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Richard said:
I need help adding a table. But I am being asked to use autonumber for
the
key but they want the key to start at 100 and increase by 5 for each new
record. Please note I am a novice and don't have programming experience.
I
was told that in prior version of access I could specify the start
number.
How can I do this in access 2007
Click to expand...
Click to expand...
 
Thanks very much for the information

Allen Browne said:
The Access interface doesn't do this for you. You will need to set the Seed
and Increment properties of the column programmatically.

Simplest way is to:
1. Create a new query.

2. In first dialog, choose Design view.

3. Cancel the second dialog (Add Table.)

4. Switch to SQL View (left of ribbon.)

5. Paste this in:
ALTER TABLE [Table1] ALTER COLUMN [ID] COUNTER(100, 5);

6. Substitute your table name for Table1, and the name of your autonumber
field for ID.

7. Run the query.

No need to save the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Richard said:
I need help adding a table. But I am being asked to use autonumber for the
key but they want the key to start at 100 and increase by 5 for each new
record. Please note I am a novice and don't have programming experience.
I
was told that in prior version of access I could specify the start number.
How can I do this in access 2007
Click to expand...
Click to expand...
 
Back
Top