Building a Query that can extract data and sum

[Sara Bellum]

Hello
I have a database that is comprised of raw material and finished material. I have a table that contains the information regarding the raw material, (raw material ID, supply, manufacturer, cost etc). I have multiple tables that contains the information of raw material that was used in the finished material (finished ID, raw material ID, amount used, etc). Multiple raw materials are used in the finished material. Ultimately I would like to create a report that gathers all of the raw materials that were used to make a finished material. I have created a query for each raw material that is used to come up with the price for each product. Cost of raw material multiplied by the volume used but this will give me the total for each type of material used and not an overall cost. How do I come up with the overall cost to product finished material (say like finished product Y in the example)? I have tried creating a basic query of one raw material to the finished product but I get the same value repeated over multiple rows in the query table. Please any help would be greatly appreciated.

Example
Raw Material A table
Raw Material ID Supply Manufacturer Cost
1 Shampoo Acme 1.00
2 Lotion SomeManufacturer 2.00


Raw Material A Data table


Raw Material ID Finished Product Volume
1 X 10
1 Y 20

Raw Material B table
Raw Material ID Supply Manufacturer Cost
B Bottles Waterford 3.00
C Bottles Waterford 3.50

Raw Material B Data table
Raw Material ID Finished Product Volume Used
B X 10
B Y 10
C Y 20


Finished Product table
Finished Product ID Product
X Shampoo in bottles
Y Lotion in bottles

Thanks,

SB

[orange]

I recommend you work through this tutorial to help get you database structured.
http://www.rogersaccesslibrary.com/T...lationship.zip

[Sara Bellum]

I hope this screen shot will clarify what I am trying to accomplish. The bottle_cost_q works how it is designed however now I am wanting sum the bottle cost (BtlCost expression) for one final product (Product_Lot_No)

[orange]

Review the Sigma --- Totals query

[Sara Bellum]

I understand that I can apply an advanced filter and then sum using the sigma. I have multiple types of supplies that make up the final. I may have another series of supply tables similar to bottles but it will be for labels. Now, how can I take the the sum of each type of supply for a finished product and apply it to a report? I want the report for finished product to have the sum from bottle query and label query, etc.

Thanks...

[Sara Bellum]

Figured it out. - don't know why I had such a hang up on this one.

SELECT Final_Product_Info.Product_Lot_No, Bottle_Data.Bottle_ID, Bottle_Data.Cs_Used, Bottle_Price.Cs_Cost, [Bottle_Data]![Cs_Used]*[Bottle_Price]![Cs_Cost] AS BtlCost
FROM (Bottle_Info INNER JOIN (Bottle_Price INNER JOIN Bottle_Data ON Bottle_Price.Bottle_ID = Bottle_Data.Bottle_ID) ON Bottle_Info.Bottle_No = Bottle_Price.Bottle_No) INNER JOIN Final_Product_Info ON Bottle_Data.Product_Lot_No = Final_Product_Info.Product_Lot_No;