Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you set AutoNumber as a fields data type, it will automatically start at one and then increment up from there, but sometimes you may need it to start at a larger number, here's how to set that up. I am going to close this Employees table and open up my SalesReps, it's essential to implement this technique, when you first create you new table. After you've assigned records to it, it's impossible to change the AutoNumbers that have been assigned. So let's say for example, that instead of one, I want all my AutoNumbers in my SalesReps table to started at a thousand and work up from there.
So after you finish creating your table, but before you begin populating it, close it and make a copy of it, I'll right-click on SalesReps and Copy and then right-click below and then Paste. I'll leave the name copy of SalesReps, so that I can distinguish the two and I'll make sure to copy over only the structure I don't want any existing data in it and I'll click OK, and now I have a second copy and now I'll right-click on it and let's go into Design View.
Change the data type of the primary key from AutoNumber to just a plain Number, make sure that the field size sales Long Integer; switch to Datasheet View and Save the table. In the first record we'll enter a number in the primary key field that's one before the number that we want to start with, so because I'm going to want to start with a thousand, I'm going to enter in 999. Enter in a few more fields of data, this is just a placeholder record and it will be deleted in a little while, close the table, I'll right-click on it and choose Close.
Now we need to create an Append Query, go up to the Create ribbon and choose Query Design. In a selector double-click on Copy Of SalesReps and click Close, double-click on the star and that will add all of the fields to the Query, up in the ribbon change the Query type to Append, it'll ask me where I want to Append my records and I'm going to Append them to my original SalesReps table and I'll click OK. So what this is going to do, it's going to take that 999 field that I created in my copy of SalesReps and it's going to add it to my SalesReps table, up on the ribbon go ahead and choose Run and it says, You are about to append 1 row, I'll click Yes, I don't need this query anymore, so I'll close it and I won't save it.
I also don't need my duplicated table, so I delete that one as well. Open up your SalesReps table and you now see your first record with the 999 and the placeholder text, delete that record, I'll right-click on the Table Selector and choose Delete Record and it asked if I want to delete it and I'll say Yes. Now when I create my next new record, it starts at a thousand, this slick trick will give you one more level of customization to your database solutions by allowing you to control your starting AutoNumber.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74525 Viewers
80 Video lessons · 129748 Viewers
52 Video lessons · 63972 Viewers
59 Video lessons · 49746 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.