AnsweredAssumed Answered

How to sum same field across multiple records

Question asked by jeffmdm on Aug 6, 2018
Latest reply on Aug 11, 2018 by Mike_Mitchell

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?

many thanks!