Solved

Add an autonumber field in Access with existing data

Posted on 2014-10-10
2
240 Views
Last Modified: 2014-10-13
Greeting,

I have data in an Access 2007 table. How do I add an auto-number field to it and make the number start with 000001?
Thanks.
0
Comment
Question by:mrong
2 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 40374079
Since the Auto Number is a  Long Integer, there will not be a any leading zeros. You can use the Format property in the Field in the Table to make it 'look' that way, but it will be stored w/o the leading zeros.

If you add a new AN field to your table, it will automatically start at 1

mx
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40377100
The autonumber is a tool used to allow you to define an arbitrary surrogate key.  It is NOT meaningful data.  Therefore, it doesn't matter where it starts although as Joe indicated, a new autonumber will start with 1.  Autonumbers always develop gaps over time.  The only thing they are useful for, aside from their stated purpose of unique identifier, is to show the order in which rows were added to the table.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now