Cascading Changes

[hhoward]

I have a DB that I need to cascade the changes on as follows

Company
Contact
Job
Invoice
Payment

Company and Contact are connected via Account Number.
Account Number is my PKey in the Company table and I have
it set to cascade changes.

Contact to Job is where the trouble starts. I have them
connected via Account Number and Contact Name. I would
like changes in either field to cascade to the equivilant
job fields. Because there are duplicate values in both
fields, I am not sure how to go about this.

I do have a primary key field called pkey in the contact
table and a field called reference number in the job table.

My data entry person is Computer Illiterate so relying on
her to remember to change the fields in each of the tables
when there is a change is too much to ask.

Any help would be appreciated.

Thanks
Holly

 

[Scott McDaniel]

Why do you need to Cascade Update your tables ... if you've properly related
and linked your tables, there's is no need to change the value you have
stored in the Jobs tables unless you made an error during data entry (i.e.
you opened a Job record for the wrong company) OR you change the Primary Key
of the related record (which should be avoided at all cost).

Basically, your Jobs table would store the value of the PRIMARY KEY of the
company for whom you are doing the job. It would NOT store the company name,
or the contact name, etcetc ... these values would be stored in their
respective tables, and then if you had need to update those records in their
tables, your changes would "cascade up" simply because your tables were
related ...

A company can have many different contacts (I assume)
A company can have many different jobs
A job can have many invoices
An Invoice can have many payments

You must determine the relationship between company - contacts - jobs ...

If you can have a job related to a specific company AND a particular contact
in that company, then you need to store the PK of the Company and Contact in
the Jobs table.

If you have jobs related to Companies, but those jobs have NO relationship
whatsoever to Contacts, then you need to store the PK of the Company in the
Jobs table, but you don't need to store the PK of the Contacts table in the
Jobs table.

 

[John Vinson]

Contact to Job is where the trouble starts. I have them
connected via Account Number and Contact Name. I would
like changes in either field to cascade to the equivilant
job fields. Because there are duplicate values in both
fields, I am not sure how to go about this.

Well... the contact name SHOULD NOT EXIST in the Job table. The job
table should have the ContactID; if you need to see the contact name
or the like, use a Query to link the tables.

Just don't store data redundantly! Store name information *once*, and
don't store it anyplace else.