Make Existing Field AutoNumber or Other Options

[GL]

I have a table that has an autonumber field called InventoryID. There is
another field in that table that is also unique, its called LicensePlate#.
The original design needed the License Plate # to be entered, but now the
needs have changed and the license plate # needs to autonumber, incrementing
from the existing data. Ideally I'd like to make this field be my primary
key and have it autonumber, but keep the existing data. Based on what I've
read I don't think this is possible, but #1 want to make sure. You should
also know I could actually delete the InventoryID because there are no
relationships using it.

I also thought about just trying to have it autoincrement the license plate
# upon adding to the table, but the problem is I add to the table from many
places, two of which are append queries coming from other tables and one is a
regular add to the table itself via a form, so I'm not sure how I could do
that.

I did try to set the next InventoryID# to be one more than the current
license plate# using the online how to: Change the starting value of an
AutoNumber field - but couldn't get this to work either! That would probably
be my last choice as then I'd have the license plate # in two seperate
fields, but eventually when the old stuff is gone it would work right, so it
would be ok.

Any help would be SO appreciated!!

 

[Ken Sheridan]

Firstly, the LicensePlate# column must be a number data type and have unique
non-null values for you to insert its current values into an autonumber
column. Assuming this is the case then do the following:

1. Create an empty copy of the existing table, delete the InventoryID
column and change the LicensePlate# column's data type to autonumber.

2. Create an 'append' query based on the original table and append its rows
into the new table. The LicensePlate# values will be inserted into the
autonumber LicensePlate# column in the new table. You can then delete the
original table, and rename the new one back to the original's name. You'll
need to recreate any relationships it’s a part of.

When new rows are inserted into the new table the LicensePlate# values will
be inserted automatically. Bear in mind, however, that an autonumber is only
intended to guarantee unique values not sequential ones, so you might get
gaps in the sequence if, for instance, a user starts to enter a new record
then abandons it. If you need to guarantee an unbroken sequence then you'll
need to use a normal long integer data type column and compute the next
number when a row is inserted into the table. There are various ways of
doing this, one of which you'll find at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


which also allows the next number at which the sequence will start to be
reset via the form.

Ken Sheridan
Stafford, England

 

[Allen Browne]

It is possible to turn the InventoryID from a Number field into an
AutoNumber field, but you might want to consider these things before you do:

a) Access assigns the AutoNumber at the time you start adding a new record.
If you abort the new entry, the number is not reused (unless you compact the
database, and even then only in some versions/service packs.) Consequently
AutoNumbers are generally not a complete sequence. Would this be a problem?

b) Could there ever be a valid duplicate? For example, could an old obsolete
number be reused some time in the future?

The process to convert your field to AutoNumber would be like this:
1. In the database window, click the table, copy (Ctrl+C), and paste
(Ctrl+V.) Access will ask whether you want to copy the data and/or
structure. Choose Structure only. Supply a new name for the table.

2. Create a query using the old table.
Change it to an Append query (Append on Query menu.)
Answer that you want to append to the new table.
Access adds an Append row to the query design grid.

3. In the Append row under your AutoNumber field, choose:
[LicensePlate#]
Under the other fields, choose the matching fields.
Run the query.

The new table now has the same data as the old table, but the AutoNumber
field contains the numbers from the license place # field. You can delete
the
[LicensePlate#] field from the new table, and just go on with the autonumber
instead (renaming it if you wish.)