Desperate! Please Help!!

  • Thread starter Thread starter KT
  • Start date Start date
K

KT

I realized the creator of the database I am working on used a text
field to link the main table with another table. I am trying to clean
up this table and set up an autonumber field to use as a primary key.
Is it possible for Access to look at the text field and automatically
set a numeric primary key to the record? (I have already clean up all
alphanumeric data in the text field, now it contains only numeric
data)

Sound quite impossible to me....any suggestions will be appreciated.
I am just desperate..
 
The first table can be set as Autonumber and the second table can be number
and the two can then be linked. Is there any special meaning to the ID
number in the second table?
 
I realized the creator of the database I am working on used a text
field to link the main table with another table. I am trying to clean
up this table and set up an autonumber field to use as a primary key.
Is it possible for Access to look at the text field and automatically
set a numeric primary key to the record? (I have already clean up all
alphanumeric data in the text field, now it contains only numeric
data)

Sound quite impossible to me....any suggestions will be appreciated.
I am just desperate..
Click to expand...

It's actually quite possible.

Create a new, empty table by copying and pasting your current table,
design mode only. Change the datatype of the primary key field from
Text to Autonumber.

Then run an Append query, appending your existing data into the new
table. If the current contents of the field are unique and numeric,
they'll be inserted into the Autonumber field; records added
thereafter will start autonumbering at one more than the largest
existing value.

Note that Autonumbers have one purpose, and one purpose ONLY: to
provide a unique key value. They are not guaranteed to be sequential;
they WILL have gaps; they can even become random, say if you Replicate
the database. As such they should generally NOT be used in any way
which involves showing them to people. As behind-the-scenes ID and
linking fields they're fine, if you have made other provisions to
ensure that you don't enter duplicate data.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top