My database contains all the people married, baptised, buried in the Church that I work for. It also contains details of services, choir members, congregation etc. I want to make this flat table relational.

In the main table I want Name, address etc. I want to create related tables for the details of specific events (weddings, baptisms). If I kept all the information in the Main table there would be too many fields.

I want to set the 'Name' as the primary key (I have no duplicates) and relate all the tables through this field.

I want the 'Name field' in every related table to be updated when it is changed in the main table (I understand I can do this through referential integrity) BUT I also want new fields entered into the Main table to cascade up to the related tables.

Can anyone tell me how to do this?

Name is a poor choice for a primary key. For one thing, in order to ensure uniqueness, you have probably mushed all the name parts into a single field. Your Member table should have a structure similar to:

MemberID (autonumber primary key)
Prefix
FirstName
MiddleName
LastName
Suffix
FamilyMemberID (foreign key to MemberID in this table)
AddressID (foreign key to Address table)
etc.


BUT I also want new fields entered into the Main table to cascade up to the related tables I don't understand what you are asking for here. Only the primary key is cascaded and as I already said, name is a really poor choice as a pk. If you go with the autonumber, no cascading will ever need to happen since autonumbers cannot be changed.

I added two concepts to the member table. A field that can be used to relate family members and a field that links to a separate address table. The reason for this is one of efficiency. Many addresses are shared by family members. Your maintenance is reduced if you can change the address once rather than 5 times (once for each family member).