How to set auto ID number that starts with say 600 instead of 1 in a table?

[Ruth Liew]

I have a table with its ID number manually keyed in that starts with number 600. Tired of keying in manually, how do I reset the ID running as autonumber and to start at number 600 instead of 1. Appreciate feedback.

[RuralGuy]

Perhaps this link will be helpful: http://allenbrowne.com/ser-26.html

[Dal Jeanis]

Two mutually exclusive terms - "Manually keyed" and "autonumber".

The user should never care what the value of an autonumber field is. The fact that you're asking for it makes me wonder if you're making assumptions that aren't right. For one thing, autonumbers are not sequentially assigned. Sometimes they will be, but not consistently.

If you plan on using them for anything specific, you had best explain the need and ask whether they will work, because Microsoft doesn't guarantee anything other than they are unique. They aren't even guaranteed to always be higher than the highest autonumber in the database for that field.

[Ruth Liew]

Rural Guy,

Thanks for the link but I'm not familiar with using SQL. How can I solve it other way?

Dal,
I need the auto number as my company's letter's reference number. (e.g. L/600). Hence, the auto number matters a lot.

[RuralGuy]

Maybe this is what you are looking for: http://www.baldyweb.com/CustomAutonumber.htm

[ItsMe]

You can build an Append query using the Access query builder. Build it like a SELECT query and then turn it into an append query.

Make a copy of your DB and practice.


Make sure you are working from a copy .

Create a copy of the table you will be working with. You can delete the Autonumber column and then replace it with the same name field only make a long integer.

Delete all of the records. Type 599 into your new Long Integer PK field. You can place other data in some of the other fields if you wish. Then you can build your SELECT query off of this table. Turn your SELECT query into an Action query, an append query that will append records to your other table.

Delete the records from your first table, the one you want to start at 600. Do a compact and repair. Then RUN your new append query.

The append query will place 599 in the original table's PK field

Now you can append the original data into the table using another append query.

[ItsMe]

I didn't type fast enough....

[RuralGuy]

Oops...sorry for jumping in too soon.

[Dal Jeanis]

Ruth - an "autonumber" is not guaranteed to be sequential. You need a synthetic key. If the key has to have some particular format, and be numbered sequentially, then an autonumber is exactly NOT what you need. See the link in post #5 by Ruralguy.

[ItsMe]

Ruth - an "autonumber" is not guaranteed to be sequential. You need a synthetic key. If the key has to have some particular format, and be numbered sequentially, then an autonumber is exactly NOT what you need. See the link in post #5 by Ruralguy.

Hmmm... I was missing that part. Would something like formatting the Autonumber field at a form or report level work? Something like

> & strCompany & "/"

This way you could have a sequential searchable number for each company and just display the company code when needed. Not sure I understand what the OP needs though. Sounds like each company needs its own series of sequential PK's. Whatever the solution, it does not sound like fun.

[Dal Jeanis]

Ruth - We can help you work out a method, but we need to know some business requirements first.
1) Do the numbers have to be precisely sequential? In other words, does it matter if occasionally you miss a number?
2) Do you have a single screen that creates each new record in that table, or are the records created in lots of different places?
3) Did I understand correctly that each company has a different qualifier letter in front of this key field? Is the letter always the same for a given company? How many companies are involved?
4) Can two different companies have the same number but a different letter? Or is it okay if the same number is never reused between different companies, like Company L gets L602 and L603 then Company M gets M604?

[Ruth Liew]

Dal,
My answer:
1) Yes, numbers has to be precisely sequential. Cannot miss a number because it will be a letter's reference no.
2) Yes, single screen.
3) Only 1 company. It always starts with L/(ref. no.). But in access table, we only have the autonumber without a letter in front. It's only in our company's actual letter, we type in L/(ref. no.).
4) ~refer answer 3 ~

[Dal Jeanis]

Ruth-

Just to be clear about the requirement for sequential numbering - What is the bad thing that would happen if a particular letter number happened to be skipped? Let's say there is one letter with reference number L621 and the next one gets the number L623. There is no L622. Do the auditors demand someone's head?

Related questions: Does a letter ever get assigned and then not sent? Is anyone going to be checking for missed numbers?

Autonumber is the easiest method, but occasionally will result in a number that is skipped. If that is acceptable, then just use autonumber and append the L. (Technically, that would be "pre-pend the L", but you knew what I meant.)

On the other hand, if the numbers have to be precisely sequential, then you don't need an autonumber. You need to build a custom autonumber feature such as the one in the link in post #5.

Assuming that you can get away with the autonumber, here's the method of getting there from here.

1) Back up your database.
2) Copy the existing table to a backup name, data + structure.
3) Delete all data from the existing table.
4) Change the number field on the existing table to an autonumber field.
5) Append all the data from the backup table into the existing table.

Access is a little persnickety about loading data into autonumber fields, or modifying autonumber fields, but I've had success with this sort of method. The key is to put in the autonumber data on initial load, rather than trying to add it later or change a non-autonumber field to autonumber.