Using MS Access/Relationship

Advertisement


Question
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:

a) Unique
b) Unchanging

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

Answer
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.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.