Adding same field data to many tables

[durbslaw]

Hi
In a nutshell I would like to know if there is a way to add the same field data to multiple tables simaltaneously.
I have tried linking the tables and enforcing referential integrity,with cascade update related fields.

Thanx

 

[FoFa]

You maybe better served by keeping this data in a seperate table with a KEY. Then using a foreign key reference from the other table to this data. Then if you do change the data, it is changed for all the other tables since they will just reference the data in the one table.

Tbl1
Key
Fkey

Tbl2
Key
Fkey

Tbl23
Key

THe Fkey in Tbl1 and Tbl2 would just POINT to the data in TBl3. You can join these tables for other purposes

 

[Pat Hartman]

Cascade update doesn't update data fields, it ONLY updates foreign key fields. If your Customer table contains the primary key CustID and the Order table contains CustID and a relationship has been defined between the two tables on the CustID field, the CustID field in the Order table becomes a "foreign" key. If you change a CustID in the Customer table from ABC to ABX, the foreign key values in the related records of the Order table will be changed from ABC to ABX in order to maintain the relationship.

Given this same db structure, if you duplicated the CustomerName field in the Order table, it would not be automatically changed when you change the value of a CustomerName in the customer table. This duplication would violate second normal form and therefore, there would be no built in tool to help you. The solution is to remove the CustomerName from the Order table. Whenever you want to display an Order with the relevant Customer information, use a query that joins the Order table to the Customer table on CustID. That will make available to your form or report, ALL columns from both tables.

 

[durbslaw]

A little more clarity PLEASE

Thanx for the reply

Assuming the tables were as follows:
Billingdetails (p.k firstname)
Clientdetails (p.k firstname)
Orderdetails (p.k firstname)
Should I create a fourth table called Update with only the fields with the same data.?
Which KEY did you refer to as KEY in?
Tbl1
KEY
Foreign KEY

Also assume I created the foreign key by dragging the primary key field from one table to another in tools menu > relationship > ADD menu.

Regards.
xxx

 

[Mile-O]

Assuming the tables were as follows:
Billingdetails (p.k firstname)
Clientdetails (p.k firstname)
Orderdetails (p.k firstname)

Based on that, I'd say you have a conceptual problem with relational databases.

First of all, the repetition of firstname as a primary key makes it look as though you have defined a one-to-one relationship between all these tables when Clients, no doubt, can have many orders.

 

[FoFa]

Assuming the tables were as follows:
Billingdetails (p.k firstname)
Clientdetails (p.k firstname)
Orderdetails (p.k firstname)

Change all your keys to say an autonumber field (a true unique key, we will just say autonumber for now). Firstname most likely will not be a unique key.
Billingdetails (p.k BillDetailID, fk ClientID)
Clientdetails (p.k ClientID)
Orderdetails (p.k OrdDetailID, fk ClientID)

Now you can have a fooreign key in your OrderDetails and BillingDetails table that points to a row in the clientdetails (assumption ClientDetails is a single client table and not part of multi-Client tables). So if you say change the Client Address in the ClientDetails table, it is automagically changed for anything with the same foreign key because the data is NOT duplicated.

Picture it like this:

BD - Bill#1 for Client #4
OD - Ord#6 for CLient #8

Clients
#1 Bob Denver
#2 Billy Walker
#6 June Cassidy
#8 Jae Moore

Since the foreign key for Ord #6 points to client #8, if you change the name on Client #8 from Jae to Jay, it is also changed for Ord #6 because it just POINTS to client #8's data.
A query to pull that would look something like this:
Select OrderNumber, Client name
from OrderDetails
inner join ClientDetails on
ClientDetails.ClientID = OrderDetails.ClientID