Insert value of field automatically from another table

[Rose]

Hi..
Is there ability to insert value of field from another table . Say for example we have Orders and Products tables
Products[ID,Name,Unit Price, Description]
Orders[ID,CustomerID,ProductID, Quantity,Total]
I would like the value of Total field in Orders table inserted automatically or default value as =[Products]![Unit Price]* Quantity



Thanks in advance .

[John_G]

Hi -

My suggestion - do away with the Total field entirely. It is a calculated field, and keeping calculated fields in a table is not good database practice. However, I suggest too that you keep Unit_Price in the Orders table as well as in the Products table. At first this may seem like a strange idea, but bear in mind that the price of a product can, and does, change. If you keep the price only in the Products table, and then change it, then all the orders for that product change too - NOT what you want (I hope!). By keeping the Unit Price in the Orders table, you record the price at the time the order was placed.

Now, one other thing to consider - what do you do if a customer orders more than one product at a time? I think you are going to have to modify your database structure to have an Order_Detail table, perhaps something like this: Order_Detail[Order_ID, Line_Number, Product_ID, Quantity, Unit_Price], leaving only data pertaining to the entire order in the Orders table.

HTH

John