Update Querry

[oscarooko]

I have these three tables A, B, C and D. IN table A, SSN is the primary key. Tables B, C, and D have other columns, but the also have SSN as one of the columns. Also SSN is NOT required in other tables.

What I need to do is to be able to run an update querry that will update the value of the SSN in table A, and in any other table that will have that SSN.

I use Access 2000

 

[Pat Hartman]

1. SSN should never be used as a primary key. There are a number of reasons. Search here and on the web for threads that discuss why SSN as a key is a poor idea.
2. Add a new primary key to table A. It is best to use an autonumber. Make a unique index for the SSN field to prevent duplicates.
3. Add a long integer column to each of the other three tables.
4. You'll need to create three update queries. One each for B, C, and D. The queries will join A to B on SSN, A to C on SSN, and A to D on SSN and update the new long integer fields in B, C, and D with the new autonumber primary key value from table A.
5. Now comes the tough part. Open the relationships window and draw relationships A to B, A to C, A to D on the primary key of A to the foreign key in each related table. Select the enforce RI and Cascade Delete. If Access complains, you will need to remove the problem rows from B, C, and D. Then go back and enforce RI. It is CRITICAL that you be able to enforce RI so keep cleaning up the data until you can. You can use "unmatched" queries to help you.
6. Once everything is clean, remove the extraneous SSN columns from tables B, C, and D