Change a number to Autonumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Yes, I know I can't do that! Here's what I've got:
tblVehicleMakes - lists car makes with ID# (which I shoulld have set up as
an Autonumber but didn't)
tblContacts
tblContactVehicles - what vehicles does each Contact own/show
tblCarShowRegistrations - what vehicles/contacts/payments happened when.

1 Contact many Vehicles
1 VehicleMake many ContactVehicles
1 Contact many CarShowRegistrations
Rick Willingham has provided lots of help to get me to this point - as have
lots of others.

So should I just make the ID tblVehicleMakes a numberthat auto-increments,
like in the "Autonumber problem" post below (11/9) or should I add an
autonumber field to tblVehicleMakes and somehow create new ContactVehicle and
CarShowRegis tables? I'm planning on a Version 2 of my database in the
future, but still will have to do the work.

This is a real how NOT to do it example.
 
Remember that an autonumber has no meaning; it serves only to identify a
unique record and provide linking. If that's all you require of your ID
field (I'd suggest losing the non-standard # character), then here's how to
add such a field:
First, remove all relationships this field is part of.
Then, add an autonumber field (I'll call it ID) to your table. Existing
rows will fill with numbers.
Now, for each table which had been in a relationship with the table
you're changing, run an update query to change the foreign key from the
value in the old (ID#) field to the value in the new (autonumber ID) field.
(Post back if you need details on how to do this.)
Finally, recreate the relationships you destroyed in the first step.
As a last touch, you can remove the ID# field if you're sure it's not
information you'll want later.

HTH
- Turtle
 
Hi Turtle - thanks for the nice clear answer! Believe me, if I can't make it
work, I'll be back in touch! And one thing I have learned so far - I'll do
all the messing around in a copy! - Thanks again - Lisa
 
Back
Top