Storing attributes derived from other attributes

[Chunk]

I want to create an attribute called "order_line_price", which is basically the quantity field * the cost per product.

I have written the following SQL query which works fine:

SELECT Order_line.order_number, Order_line.product_number, product_name, quantity, (quantity*price) AS [Order line price]
FROM (Order_line INNER JOIN Product ON Order_line.product_number=Product.product_number) INNER JOIN Price_band ON Product.price_band=Price_band.price_band;

I can create a table from this query, and all is ok. However, I want to store the value of each order_line_price next in its appropriate row in my Order_line table. I cant do this no matter what I try. I know that what im trying to do is store a derived coloumn, however I need to do it, because I have an attribute elsewhere which is the SUM(order_line_price), which I want to limit by specifying the order_number for which to add the order_line_prices up for.

So basically, how can I store a derived column like that? I guess I need to set something up in the default value property and lock the coloumn or something?

Ive posted here before, and havent had replys. So if your reading this and dont know the answer, but do know the link to a forum or chat room with speedier replies, please post the link.

Thanks.

 

[DNS809904]

Hello,
Well I am definitely not the authority on this... but since you haven't heard from anybody else I figured you'd appreciate any response on the matter.
Basically, what I have learned about calculated fields, is that you should never store them in an actual table. You are on the right track by having it in a query. Then, when you need to calcluate the sum of those individual subtotals, depending on where you want to display them, such as on a form, you can use the DSum function using your stored query as the source just like you would use an actual table, and use that as the control source for a text field or label or whatever. Here is a discussion of it that should help clarify things for you: http://www.eggheadcafe.com/ng/post2000917.asp
Hope this helps!
Dana