We sell kits of parts and I'm trying to learn how to add up the total cost of parts (skus) when the cost and quantity of the skus are in their own records (rows). So for example I have a table of kits:
KIT1 SKU5 $3.00 QTY 1
KIT1 SKU3 $2.00 QTY 2
KIT2 SKU3 $2.00 QTY 1
KIT2 SKU6 $7.00 QTY 3
KIT2 SKU9 $1.00 QTY 5
I have a related table with a list of all of the kits linked to the table above using the kit field as the connector. I want to show the summary of total kit cost in this related table.
The line items of a common kit represent the cost and quantity of the skus within the kit. I want to sum up the cost of e.g. KIT1 as $7.00. (qty 1 x sku5 cost $3 + qty 2 x sku3 cost $2)
I'm confused by many discussions of the SUM function as relating to a found set of records or a sorted set of records, when instead I want to do this for all of my records all of the time. The goal is to output a spreadsheet of kit costs.
I've tried using SUM but am only getting the value of one line item, not the sum of all line items for a given kit.
Can someone point me in the right direction please?