Look up wizzard on 2 fields in a Table

[Morrel]

I'm not sure if this thread should be in table or if I need a query for this.

Currently I have to tables: table 1 with material and material dimensions( Width, Length, Height and weight). In Table 2 to I have Material, Weight ,Substance and substance weight.

Material In table 2 is linked with Table 1 but I want if you select a material in table to the corresponding weight if also copied from table 1 to table 2.

Can this be done automatically in a table or do I need a query for this?

Thnx in advance

 

[Pat Hartman]

I don't understand why you have the second table if the two tables have a 1-1 relationship. To answer the specific question, data is never automatically copied from one table to another because properly defined schemas do not duplicate data. Let me use a common example since I do not understand yours. In an Order Entry application you have a table named Orders which is the header for an order. It contains the customerID, shipping address, order date, etc. - anything that occurs once per order. Then you have Order details. This table contains one row per item ordered. It contains the OrderID to connect it to its parent table and it contains ProductID to connect it to the product table. Quantity and Price are also stored in order details. Notice that none of the customer name and address information is copied into the Order table and none of the Product information is copied into Order details except for price. The reason that price is copied is that price is time sensitive. The price may be x today and y tomorrow and the Product table will always contain the current price. You would not be able to do proper billing or reporting if you didn't copy the price from the product table at the time of the order.

Cascade update, which is what you may be thinking about, will only propagate key values to foreign keys in child tables. Cascade update is only used when your primary key is user created. It is never used when a primary key is an autonumber because you can't change the value of an autonumber PK for a particular record. An example for our Order entry scenario might be - customerID's are created by a mainframe legacy system and you can't change them. They are formed by using the first four characters of a customer's last name followed by a number. Sally Smith gets married. Her new name is Sally Jones. That changes her customer ID from SMIT204 to JONE185. When you change the PK of the customer table, Access will automatically change the foreign key values of all child tables. So all orders for Sally will now contain the customer ID JONE185 instead of SMIT204.