Cascade update not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I hope someone can help me.

I have 5 tables with relationships set up as followes:
Client Details table
PK Client ID ref Spouse details table (1 to 1 relationship)
PK Erf Nr ref Property details table (1 to 1 relationship)
Name
Surname
Address
Code

Spouse Details table
Client ID PK
Spouse name
Spouse ID
Contact Nr

Property Details
Erf Nr PK
address
City
price
BankID
AttorneyID
Client ID

Financial details table
Bank ID PK ref property details table (1 to many relationship)
Financing bank name

Attorney details table
attorneyID PK ref property details table (1 to many relationship)
address
phone nr

Why, when I update the client details table do the spouse details and
property details tables not get updated?

Thanks for any help.
 
The only thing that should change are the Foreign Keys and that is only if
you actually change the value of the Primary Key.

The Primary Key you indicate for Client does not seem to make sense as the
first side of the 1 to 1 relationship.

The Primary Key for all 1 to 1 related tables should be the same.

Generally it is not a good idea to have 1 to 1 relationships unless you
understand their use.

The two main uses are for subtyping, an example of which may be an Employee
table which has 1 to 1 relationships with the type of employee they are,
especially if these different types of employee have different information
recorded about them.

The other is for optional data (not dissimilar to the above) where the data
may not be available for a good proportion of the records. Purists may argue
that if the value of any field could be unknown (null, not strictly a value)
it should be removed to a subsidiary table to achieve the goal of minimising
or eliminating nulls.

In your case I would think it may make more sense to include all the 1 to 1
data in a single table as they will either have a spouse or not and they
will either have a property or not. If you are operating an Estate Agency
database you should know the Property Status and if in the UK the
Matrimonial Homes Act (or whatever it's called) will mean that you will need
to know the marital status.

If you feel you need to keep the separate tables the following may be a
better arrangement of the data, although without knowing the full details
especially the problem you are trying to model this should be carefully
considered for appropriateness.

Table: Client
Primary Key: ClientID
Other Fields: ,Name, Surname, Address, Code

Table: ClientSpouse
Primary Key: ClientID
Other Fields: Spouse name, Contact Nr
(not sure what SpouseID is for)

Table: ClientProperty
Primary Key: ClientID
Foreign Key: BankID
Foreign Key: AttorneyID
Other Fields: Address, City

Table: Financial
Primary Key: BankID
Other Fields: Financing bank name

Table: Attorney
Primary Key: AttorneyID
Other Fields: Address, Phone nr
 
Hello Craig

Thank you for your reply and suggestions. The SpouseID is for the spouse's
own national identity number, the same with the clientID. I have considered
adding the spouse details to the client table, but thought it would be neater
if the details were in a seperate table as I could add a command button to my
client details form to add a spouse if the client has one.

I thought a 1 to 1 relationship is the only way to work here, as a client
should only have 1 spouse.... but thinking about it now, can I have a 1 to 1
relationship and not have a corresponding record in the other table?

In your example you use the ClientID as a primary 3 times, can I reference
to 3 tables using the same primary key? To clarify, could you please show me
how you would set up the relationships?

Thanks alot for you help.
 
In your example you use the ClientID as a primary 3 times, can I reference
to 3 tables using the same primary key? To clarify, could you please show
me
how you would set up the relationships?
Click to expand...

If you do not have this then you do not have 1 to 1 relationships. The only
way to uniquely identify any record in any table in the 1 to 1 relationship
is via the ClientID.

Just create your three tables with the same primary key and link them via
the relationships window. Have you actually established these relationships
via the Relationships Window and set the cascade options?
Thank you for your reply and suggestions. The SpouseID is for the
spouse's
own national identity number, the same with the clientID. I have
considered
adding the spouse details to the client table, but thought it would be
neater
if the details were in a seperate table as I could add a command button to
my
client details form to add a spouse if the client has one.
Click to expand...

You need to design the correct relational database first and then worry
about the forms. I would consider it unwise to have a separate table, but
wish you luck.
I thought a 1 to 1 relationship is the only way to work here, as a client
should only have 1 spouse.... but thinking about it now, can I have a 1 to
1
relationship and not have a corresponding record in the other table?
Click to expand...

Of course! A 1 to 1 or 1 to Many relationship also implies that a related
record is optional. The record in the first table is mandatory, of course as
you can't have one or many related "detail/child" records if there isn't a
"master/parent" record.

I would seriously consider learning a bit more about relational database
design before you proceed if the use you are going to put this database to
is non-trivial.

Access is not like Excel or Word you can't just do it! You need to carefully
design the structure of the data so that you can take full advantage of the
way Access is designed to work. With a solid and correct data structure the
applications (form/reports etc) become so much easier to create and develop.

If you provide more details of your problem domain others here may be
willing to suggest possible designs, take such advice with care as it can
only be based on what you tell them and if you miss something out that could
be important any solutions will range from slightly wrong to wildly wrong.

You may be better starting a new thread to get others to discuss the
database design as others here may well skip over this one thinking it has
been answered.


Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
mix said:
Hello Craig
Click to expand...
comments above
 
Hi Craig

Thanks for your reply and explanations. Yes, I have set up the
relationships and set cascade update related fields. I can now enter details
into my subform and the related data is cascaded down to the referencing
table (child table). I have more experience designing forms and queries than
establishing relationships between 4/5 tables. I realised it is essential to
get the relationships right from the start, so I am working on it and
appreciate all your feedback today.

Have a great day further.
 
Back
Top