updating database, transfering records.

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

Guest

Ok, I have a problem I am hoping someone can help with. I created a database
a few years ago to help me keep track of students I work with. Back then, I
decided to use the student Social Security # as the primary key. Good idea,
right. It is individual to each student. Well, now they have stopped using
them in the schools and I am just making up my own numbers for each student.
What I would really like is to set the primary key to auto number. I tried
copying my main table, deleting the data, setting the SS# to auto number, and
then pasting the data back in. What happened was that there were other
tables that are linked to it using the SS# and all of that data did not show
up. I was thinking of just adding a new column of auto number and also
adding them in the related tables and making it a shared primary key. Then,
after a while I was thinking I could delete the SS# after establishing
relationships with the auto number. It seems like a bit of work and I was
wondering if anybody saw a glitch with it before I try. I think I will have
to rewrite all of the queries I have established after I do it.

Can anybody think of another way to switch the primary key to auto number
and keep the relationships?

Heck, maybe I should rebuild the database. It was my first one and has some
glitches.

jason
 
Leave the SSN field in place to start.
Add a new field to each table of type Numeric - Long.
Add the Autonumber field to the student table.
Run an update query on each of the child tables (joined by the SSN) that
updates the new field to the value of the parent's autonumber field.
Change the relationships to join on the new fields.
 
Back
Top