starting value for autonumber in access 2007

  • Follow


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
0
Reply Utf 1/31/2008 4:49:07 AM

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <Richard@discussions.microsoft.com> wrote in message
news:6FDB0400-01AA-402A-BFC9-7FB8FE1B1F48@microsoft.com...
>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 

0
Reply Allen 1/31/2008 7:19:23 AM


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" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:Oxrqcm9YIHA.5900@TK2MSFTNGP02.phx.gbl...
> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Richard" <Richard@discussions.microsoft.com> wrote in message
> news:6FDB0400-01AA-402A-BFC9-7FB8FE1B1F48@microsoft.com...
>>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
> 


0
Reply Terry 1/31/2008 10:46:46 AM

Thanks very much for the information

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Richard" <Richard@discussions.microsoft.com> wrote in message
> news:6FDB0400-01AA-402A-BFC9-7FB8FE1B1F48@microsoft.com...
> >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 
> 
> 
0
Reply Utf 1/31/2008 2:26:01 PM

3 Replies
1621 Views

(page loaded in 0.084 seconds)


Reply: