Calculating a price from several items.

[GavRobBon]

Hi all, My first app, 

My tables

1) List of raw materials (Items).

ItemID (this UID)	Title	Price
IID1	Bolt	1
IID2	Nut	1.20
IID3	Washer	.5
IID4	Locking Nut	1.50

2) List of products made from the raw materials (Products).

ProductID (this UID)	Title	Price
PID1	Standard Fixing	This is what I want calculated
PID2	Locking Fixing	This is what I want calculated

3) List of raw materials associated with each product. (Product_Items)

ProductItemID	ProductID (ref to products table)	ItemID (ref to Items Table)	Quantity
PIID1	PID1	IID1	1
PIID2	PID1	IID2	1
PIID3	PID1	IID3	2
PIID4	PID2	IID1	1
PIID5	PID2	IID3	2
PIID6	PID2	IID4	1

I need a virtual column in Products that gives me a total price by returning the Item price (in items)* Quantity of each of the raw materials used (in the product_items table)

All the REFs: are in place, the product_items correctly links the items from Items to Products. I now just need the formula that will change as raw materials cost change.

I have to go, my 2 yr olds waking but I hope it makes sense, I will proof read tomorrow.

Thanks for your help on this.

[AlexM]

Hi,

The most explanatory way is to add in Product_Items table some VC (short for virtual column)
[Material_Price] = [ItemID].[Price] DOT BETWEEN ].[
[Item_Price] = [Material_Price]*[Quantity]

Then in Products table VC
[Price] = SUM([Related_Product_Itemss][Item_Price]) NO . BETWEEN ][


VC are to be avoided because they have a great impact over performance but for your first app and "to get there quick", they'll do.

I hope it helps!