Updating Records

[whitty15]

Hi there people...i'm doing a databae project for A2 level coursework and have encountered a problem..please help me if you can.
I have a table of names. Also another table (actions) which contains several fields, but 2 of them are names, (1 being Proposer, and other being Actionee), are obtained from the names table.
However, when altering for instance the spelling of one of the names in the names table it does not update the spelling in the actions table. i have tried using the relationship-update option but this doesn't work because the names appear more than once in the actions table. if it were to appear just the once in the actions table then i wouldn't have any problem. but it is a requirement that the 2 names fields are in the actions table.
Any ideas of how to solve this?

 

[boblarson]

The key, which you are finding out, is that you are storing the wrong information in the tables. You should be storing the primary key of the names table in the main tables. That way, you can change the name spelling in the names table and not affect any other tables. You then use a query to tie the main tables and names table back together so that you can view the data with the names.

So, for instance

Table: tblNames
NameID
Name


Table: tblActions

ActionID
ProposerID (which will use NameID and NOT name)
ActioneeID (which will use NameID and NOT name)
All of your other fields here

 

[whitty15]

It appears that what you are suggesting for me to do is to use a link a primary key within the names table to the actions table?
i currently have the persons name as the primary key...but as mentioned previously this field cannot be linked to the actions table to two fields with the 'cascade-update' selection ticked. but because of this when a record is updated in the names table, where this information appears in the actions table it is not updated, due it not being in a 'cascade-update' relationship.
would a possible solution to be to create an update query?
for example find where *certain text within a certain field* appears, and replace with *new spelling*?

 

[ejstefl]

The name really shouldn't be a primary key - it is not a unique field. Two people could very easily have the same name.

The method boblarson suggested is correct. You should create an autonumber primary key for each person, and store that key in the Action table.

 

[whitty15]

i agree that two or more people could have the same name, but for this case they will not. so surely it should still work?

 

[boblarson]

If you follow my suggestion, then you would only have to change the spelling of the name in the names table. This is correct normalization of the tables. If you choose not to go that way, then yes you will have to get more complex to change the data in multiple places. I think you'll cause yourself more pain doing it that way than if you designed it correctly.

You actually can set the cascade updates/deletes to work in the relationships for two different fields that use the same name table.

As long as the ID number in the Names table is set as the primary key, you can add set the relationship to a one to many relationship.

 

[whitty15]

So far I have changed the people table so that it has a unique number (primary key) for each person, as you recommended.
However, when going to set up the relationship for the actions table and people table i cannot enforce referential integrity and cascade update related fields when the primary key for the person is related to both proposer and actionee in the actions table. i can do this for one or the other but not both, but which ever isn't related doesn't get update when a change is made (such as a spelling change). Any ideas?

 

[boblarson]

Can you post your database (or a stripped down version) so I can take a look?

 

[whitty15]

how do i go about posting part of my database up?

 

[boblarson]

You'll need to click the POST REPLY button instead of using the Quick Reply feature. Then, make sure to zip your database and it should be under 393Kb in size.

Then scroll down the page in the reply and click on the MANAGE ATTACHMENTS button. The rest should be self-explanatory.