Relationship problem with updating related fields

[Guest]

I am having a problem with a realtionship. I have three tables which are
involved.

1) tblCompanies (non-customers)
2) tblContacts
3) tblCustomers

the pk in tblcompanies is pkComapnyID with a one to many relationship to
tblContacts. If a "company" becomes a customer an accoutn number is filled
in field acctnumber, I am needing the realtionship to cascade update
tblcontacts when this account number is entered. When I change my
relationship to include the acctnumber field I get a message that reads
"no unique index for the referenced field of the primary table". However I
have the properties set to Yes(no Duplicates),Yes in both tables with the
same field size and data type.

Once the record has an account number in tblCompanies it will moved (append
query followed by delete query) to tblCustomers. tblCustomers is linked to
tblContacts using the acctnumber field.

If anyone can help with the updating related field issue it would be a big
help.

 

[Guest]

Are all customers companies? If so you have a classic Type/Sub-Type
situation here in that the customers entity type is a sub-type of the
companies entity type. Types/Sub-Types (sometimes referred to as
Classes/Sub-Classes) are characterized by the latter sharing all the
attributes of the former, i.e. the Companies entity type has attributes
common to all companies regardless of whether the company is a customer or
not. The customers sub-type has attributes only relevant to customers, e.g.
a customer account number.

Types/Sub-Types are modelled by tables in a one-to-one relationship with
columns representing the appropriate attributes of each, so CompanyID would
be the primary key of Companies, and would also be the primary key of
Customers. In customers it would also be a foreign key referencing the
primary key of Companies. It follows therefore that while it can be an
autonumber column in the former, it cannot in the latter, where it is a
straightforward long integer number data type.

When a company becomes a customer a new row is inserted into Customers with
a CompanyID value matching that in Companies. No deletion from Companies is
necessary, or indeed permissible as it would offend referential integrity.

Contacts would have a CompanyID foreign key column referencing CompanyID in
Companies.

There might well be other sub-types of Companies. The obvious candidate
would be Suppliers which would have attributes relevant only to the Suppliers
entity type, all other attributes common to all sub-types of Companies being
in Companies.

With this logical model companies are identified as customers by virtue of
their having a matching row in Customers. A company could of course be both
a customer and a supplier by having matching rows in both Customers and
Suppliers.

Ken Sheridan
Stafford, England

 

[Guest]

Ken,

Thanks for your comments, however NO all Customers might not be in the
Company table. The company table consist of contacts from trade shows and
other data sources it would be more a "sales leads" table really. We will be
adding our current customers in the customers table with company information
held in that table only.

i would have had a seperate database for our "leads" but my supervisors
wants all the data in one database so our sales staff does not have to open
and close different databases while makeing / taking calls.

I am still haveing trouble just creating a realtionship with update related
fields integraty in place. any idea's?

Barry

 

[Guest]

Barry:

You won't be able to use a cascade update referential operation here. In
fact you can't enforce referential integrity between Companies and Contacts
at all as you are deleting rows from the former when they become customers.
To allow the enforcement of referential integrity you'd first have to delete
all matching rows from Contacts before you could delete the row from
Customers.

You can however achieve much the same ends by updating Contacts before you
delete the row from Companies. I assume this is done from a Companies form
in which the account number is entered and code or a macro behind the form
execute the delete and append queries. So what you need to do is execute an
UPDATE query before these which updates the account number column in Contacts
for all rows which match the current companyID. In code it would go like
this:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE tblContacts & _
" SET acctnumber = " & Me.acctnumber & _
" WHERE CompanyID = " & Me.pkCompanyID

cmd.CommandText = strSQL
cmd.Execute

I've called the foreign key in Contacts CompanyID so you'd have to change
this if you use a tag such as 'fk' as a prefix to the column name. I've
assumed that acctnumber and the companyID primary and foreign keys are all
number data types rather than text. If any are text you'd need to amend the
code which builds the SQL statement so that the values are wrapped in quotes.

Ken Sheridan
Stafford, England