Updating a relationship

[damie]

I'm sure this is already answered somewhere but didn't know what to look under to find it - sorry.

Let's say I have a table (1) of a list of projects carried out in 5 towns. I also have a table (2) with just those 5 towns listed and a one-to-many realationship with table (1).

I want to change the name of one town in table (2) with the desire that all of the corresponding lines in table (1) change town name as well.

When I try changing the name in table (2) it tells me I can't because of the relationship I have with table (1), but I don't want to remove the relationship because I want table (1) to update itself, too.

Can someone please tell me what I need to do to avoid having to manually update the first table which I would assume should be unnecessary.

Thanks!!

 

[Mile-O]

Can you clarify your table structure? i.e. list the fields of each table.

 

[damie]

Well, in Table (1) I'd have Project Name (Primary Key), Town Name, Services and Value. In table (2) I'd just have Town Name (PK).

If I change a town name in table (2) from, let's say Bristol to Malaga, I want table (1) to change all it's Bristol entries to Malaga as well (that is possible isn't it?).

Thanks.

 

[Mile-O]

Well, in Table (1) I'd have Project Name (Primary Key), Town Name, Services and Value. In table (2) I'd just have Town Name (PK).

If I change a town name in table (2) from, let's say Bristol to Malaga, I want table (1) to change all it's Bristol entries to Malaga as well (that is possible isn't it?).

Yes, it's possible. I'd advise against having text fields as primary keys though. The more information your database holds, the slower it is going to be to index and query.

I'd have:

tblProjects
ProjectID - autonumber
ProjectName - text
TownID - number
(I don't know what Services and Value are; Services being a plural seems suspect)

tblTowns
TownID - autonumber
Town - text

Then, in the relationships, join the two TownID fields, enforce referential integrity, and Cascade Deletes

 

[damie]

Thanks very much for that. Great help!

Damie.