starting value for autonumber in access 2007

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
Utf
1/31/2008 4:49:07 AM
access 16762 articles. 2 followers. Follow

3 Replies
2361 Views

Similar Articles

[PageSpeed] 59

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
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
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
Utf
1/31/2008 2:26:01 PM
Reply:

Similar Artilces:

Get the sum value for every worksheet
Let say I got more than 20 worksheets in a workbook. In B2 of Sheet1, I want a sum value of A1 in every worksheet. Insteed of add up with '+' sheet by sheet. Any faster way for it? It's good practice to insert a sheet called Start before the first 'real' sheet and a sheet called End after the last 'real' sheet, then use a formula like =SUM(Start:End!A1) to get your result. This will add A1 values in ALL sheets between the Start and End sheets - that way you can insert or delete sheets between Start & End and the formula will still work. Rgds, ScottO "Kelv...

Re: Outlook 2007 create background
I played around with this further. You can still use any HTML editor to = create stationery and place it in the user's Stationery folder. You can = also use FrontPage 2003 to create new themes with background images. = Details at = http://turtleflock-ol2007.spaces.live.com/blog/cns!C1013F1F9A99E3D8!230.e= ntry --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =...

Setting Default protocol access
I'd like to set new users to have POP3 disabled by default. I've written a script to disable everyone who's already in AD, but don't want to run the script later becasue we have a service account with a special case need for POP, and I don't want that to be disabled on accident later. Is this possible? Thanks, if nobody needs POP3 you could just turn of the service on Exchange so even though it is "enabled" for all users it isn't accessible anyway. Of course if you need a few folks to have POP access this won't work "Jacob M. Ross" <...

accessing XML data
Hi, I need to import data from XML to Access97 , what is the best way??? Is there any ODBC solution?? Thanx! Domac This was written with Access 2000 in view, but the idea should work. Check out the article at DBJ. http://www.databasejournal.com/features/msaccess/article.php/3310901 -- Danny J. Lesandrini dlesandrini@hotmail.com http://amazecreations.com/datafast "Domac" <dd.cc@cc.cc> wrote ... > Hi, > > I need to import data from XML to Access97 , what is the best way??? > > Is there any ODBC solution?? > > Thanx! > Domac > ...

Where does Outlook 2007 store emails locally?
We are using Outlook 2007 with Exchange server. We have a user who has been moving his emails off of the Exchange server to some folders he created or by Auto Archive feature in Outlook 2007. He basically drag and drop the emails from his Exchange server Inbox to his folders on the left side. The question is, where are all of these folders full of emails located on the local computer? At first I thought it must have been the Auto Archive feature that kicks in every 14 days and created those folders for him to be able to drag and drop, but when I check the following location the archi...

Bar Chart with Date X-Values
I am charting weekly sales totals for a list of products. The data is in a table with the products in the A column and week-ending dates (formatted as dates) in Row 1. The table uses a formula that references the dates in row 1 to find the data in other parts of the workbook. When I make the chart, using row 1 and the X-range, Excell sees date missing and puts gaps between my data. (the dates are 7-11, 7-18, 7-25 etc.) Is there a way to avoid this? I know I can do it by formatting the dates as text, but then my lookup formula won't work. TIA Marcotte Marcotte - Select the cha...

Starting up on start-up help!
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello all, <br><br>I'm new here so Hello. I just have a question to see if anyone can help here. I just recently bought a new MacBook Pro, and I got the Microsoft Office for it too. Well, I recently noticed that on start up, entourages My Day keeps opening up. Well when I right click the menu, I check off the box where it says to start on login. <br><br>Well here is the problem. Everytime I uncheck it, it still keeps opening up on start up. <br><br>Anyone know why?...

Move cell values
Hi, Need a help in Macros. Suppose i have the foll. data ColA Col B 1 w 2 re 3 dff --> Empty cells 1 ed 2 23 3 24 I need to copy the cell with 3 in colA two rows up, so it should be someting like this: ColA Col B 1 w 3 dff 2 re 3 dff --> Empty cells 1 ed 3 24 2 23 3 24 Thanks for help. Hi ........., I think you messed up your...

Formatting default Entity and Value
In ORM Source documents is it possible to set the default background colour (Fill) of Entity and Value elements? To save me having to continually setting the values manually. Cheers, McGiv Hi McGiv, > In ORM Source documents is it possible to set the default background > colour (Fill) of Entity and Value elements? To save me having to > continually setting the values manually. Sorry, no. The ORM info is separate from the shape info and as such a new shape is created every time you drop a given ORM object on a page. If you want them all to be a given color (which I don't thi...

Need advice to start off a project
Hello! I have never really used Excel much but I have had an idea which could make some of the work I do a lot easier. I know Excel is powerful but I don't know the best way to approach it so can anyone out there help me? I make visits to places and record what kind of visit it was on a spreadsheet with the values in each cell being the date. At the bottom there is a summary box which shows, on each date, how many visits I made of each type. At the moment this is done manually but I am sure it can be done automatically only how? On a different sheet I would then like to have a summary of...

Update query based on a form value
Hi, I have a form with a textbox and a combo box. The text box is called CompanyName and the combobox is called CompanyFamilyName. Every CompanyName eventually needs to be assigned to a CompanyFamilyName. However some CompanyNames have a "unassigned" value to the CompanyFamilyName to start with. What I want to do is to give the user the ability to assign a new CompanyFamilyName or to select a CompanyFamilyName from a combo box containing a list of existing CompanyFamilyNames. I have this piece taken care of. I also want the the CompanyFamilyName in the "C...

Inventory value problem
How do I display the current value of my inventory? The balance sheet only gives one word, "Assets". For tax purposes I need to know the inventory value as a separate item. the Help file doesn't say anything about where to find inventory values. Bob ...

Cannot start Outlook 2002
My computer is usualy connected to an MS Exchange server at work. However I'd like to use Outlook at home with another e-mail account. When I try to start Outlook I get an error message : Cannot connect to exchange server. So I click work offline. Then, get another error message: Cannot start Outlook. Could not open information store. The the program quits. Anyone know a work around? Thanks. Create a new profile for your home account. Then set Outlook to prompt for profile. Select your home profile and Outlook will not try to connect to Exchange. --� Milly Staples [MVP - Outlook...

display var value
Hi, i want to display a value of a variabile, type into a textbox. for example num=1234 varx=763 if i type in textbox "num" i want to dispay 1234 if i type in textbox "varx" i want to display 763 who can i help???? On 26 mei, 11:57, Clax <claxc...@gmail.com> wrote: > Hi, > i want to display a value of a variabile, type into a textbox. > > for example > > num=1234 > varx=763 > > if i type in textbox "num" i want to dispay 1234 > > if i type in textbox "varx" i want to display 763...

Can an excel cell automatically change fill colors based on values
I'm developing a customer satisfaction index. My question is: Can the fill color of a cell automatically change to a pre-determined color based on the cells value. Example: If the cells value shows an average of 3.5 or greater the cell fill color would be green. Thanks for your help. I look forward to your response. Hi John yes, if you have three or less conditions use Format / Conditional Formatting. For more than three you can use VBA. Please feel free to post back with the conditions and required results if you would like more assistance. Regards JulieD "John Clark...

Missing Attachments in Outlook 2007/Outlook 2010 Beta.
Hi, We are experiencing a missing attachment issue in Outlook 2007/Outlook 2010 beta from Outlook 2003. These attachments are in messages forwarded as attachments and received via our Exchange 2010 installation. The attachments *are* visible from Outlook WebApp. In Outlook 2007/2010 the message has no paperclip icon, yet the message is of the correct size. From my rather long investigation, it appears that these certain messages from Outlook 2003 include a Content-ID header, which is typically used for referencing embedded attachments, such as inline JPGs for HTML pages. ...

Excel 2007 Pivot Tables
How do I set my pivot table so that it does not display any sums of values that are equal to zero? Thanks, Barb Reinhardt You can use conditional formatting to set the foreground colour to white if the cell contents are zero. Hope this helps. Pete On Mar 10, 2:19=A0pm, Barb Reinhardt <BarbReinha...@discussions.microsoft.com> wrote: > How do I set my pivot table so that it does not display any sums of value= s > that are equal to zero? > > Thanks, > > Barb Reinhardt ...

Exchange Services Do not Start After a Restart #2
I have a SBS 2003 Running and for some reason some of my Exchange Service do not start after a restart. System Attendand tries to start shuts down unexpectedly. I can go to services right after logging in and start the services and they start with no problem. Right before the error that service did not start, I get this warning: "A service process other than the one launched by the Service Control Manager connected when starting the Microsoft Exchange System Attendant service. The Service Control Manager launched process 2448 and process 3688 connected instead. Note that if this se...

Custom CEdit class in SDI formview, How can I access other controls from custom class
Hi all, I need a little help with this if you guys don't mind . I've subclassed CEdit so that I can catch some key messages and do 'stuff' with them some of which invlovles manipulating other controls on the FormView from my CMyCEdit class. However I cannot seem to access those controls(a regular cedit at the momment). a GetDlgItem(IDC_CEDIT); from the custom class always seems to return null. I've read some other newsgroups and I understand this could be because my control could be on a different thread and cannot access the map for the rest of the Formview? I'm no...

Trouble accessing this NG
My usual contact with this NG is via msnews.microsoft.com Today, I cannot make "contact" and have resorted to my ISP's News Server. Any one know what would cause this? Rob Rob No idea. msnews.microsoft.com working fine for myself. Gord Dibben Excel MVP On Wed, 21 Jan 2004 22:40:39 GMT, "Rob Nobel" <robnobel1@bigpondNOGOOBS.comNOGOOBS> wrote: >My usual contact with this NG is via msnews.microsoft.com >Today, I cannot make "contact" and have resorted to my ISP's News Server. >Any one know what would cause this? >Rob > Yes Gord,...

Macro or Lookup? Not sure where to start.
I have created two worksheets: 1) Auction Items Item# Table# Location# ItemDescription WinBid are the headers of the data. 2) Bidders Bidder# FirstName LastName I would like to be able to create a macro or something that will allow me to enter the item#, bidder# and winning bid price and have a sheet filled with. ie. Item# 2 (Handcrafted Table) Bidder# 12 (John Doe) Winning Bid $250 Can excel pull from two sheets and created another sheet with all the final details? I am a little new to excel (version 2000) it is the only tool/application available to us at this time. If anyone can poi...

Column Break based on Record Value
Hello: I have a columnar report of employees by hire date. I'd like to be able to have column 1 break at 1/1/2007 so that all the 2007 hires are together in column 2. What's the best way to attack this? Version: Access 2007. Thank you. ...

public folder issue 2007
Succesfully upgraded to 2007 server, except im having a few issues with some public folders we have a folder with subfolders on the public folder list eg TOPLEVEL -------SUB1 -------SUB2 -------SUB3 -------SUB4 -------SUB5 however since conversion, i'm unable to access some of the subfolders (but only some!) - i get a error message saying "cannot display the folder. this folder cannot be opened because there is a configuration problem on the server. contact your microsoft exchange admin for assistance", I cant even admin the permissions from outlook either ? any ideas ? ...

Outlook starting problem
"Cannot start Microsoft Outlook. A dialog box is open. Close it and try again." ...

Running CHKDSK at start up
Is there a way to run chkdsk f/r on all five drives on one PC at start up? PC runs Windows XP Pro SP3. This way all drives would be checked and errors fixed, if necessary, in one go, at start up on this machine. We intend to do this on this heavily used PC from time to time - we do not need the desired chkdsk functionality to kick in at every boot! Regards and TIA. Avatar -------- "Avatar" <Avatar@re.birth> wrote in message news:hrb4n2$53i$1@news.eternal-september.org... > Is there a way to run chkdsk f/r on all five drives on one PC at start up? ...