Using MS Access/Relationship
QUESTION: Hello. I have a database that contains 3 tables: Account - Subaccount - Participants. A relationship exists between these 3 tables. the Subaccount has a code as Primary Key. When I need to modify an existing code (sometimes just correcting a typing mistake), the related participants list disappear and I can't re-link it again to the updated code. Kindly note that many transactions are made to the table "participant" and I am afraid that I lose important values.How can I change the code (primary Key) and re-link it again to the participants?
ANSWER: The Definition of a Primary Key is that it is:
So this begs the question of why you are changing the code for a SubAccount? You might want to review this article on natural vs surrogate keys (http://www.dbta.com/Columns/DBA-Corner/Surrogate-Keys-or-Natural-Keys-84892.aspx
). My recommendation is to use an AutoNumber field as your primary key and corresponding foreign key. This way if you need to change this "code" you are entering it doesn't affect the relationships. Another alternative is to make sure you have imposed Referential Integrity on your relationships (using the Relationships Window). Make sure Cascading Updates is checked off. This should update any child records if you change the key value of the parent record.
---------- FOLLOW-UP ----------
QUESTION: Thank you.
Don't I have any other alternative because I already have an amount of data linked to this subaccount and the client is requiring the name to be changed. Regards. Maya
There is one other alternative. After you change the name in the subaccounts table, you then run an Update query on the Participants table to change the value. But I would NOT recommend that.
What I would suggest is the following steps:
1) Add an Autonumber Primary Key to the SubAccounts table
2) Add a Long Integer Foreign Key to the participants table
3) Create an Update query joining the SubAccounts table to the Participants table on the existing key fields that will update the new FK to the Autonumber PK from the Subaccounts table.
4) Change the Relationships between the two tables to use the new Keys
5) Change any queries to the new Key and add the name feild from the SubAccounts table where needed
6) Change the Master Child links for any subforms to the new Keys.
7) Then you can change the Name in the SubAccounts table
Again, this could have been avoided by proper design initially.