I am providing a scrap analysis report, showing scrap qty, percentage and total cost grouped by part number.

The data is coming from a few different tables and once I introduce my third table the Sum of the total rejects changes. By doing some research it would appear I have to nest my select statement that SUM's the rejects. I am struggling to get this to work with the two Join statements.

Any help is appreciated, let me know if you need more information.

Select Part_Numbers.Part_Number, Part_Numbers.Part_Description, SUM(cast (Rej_Qty as int)) AS Total_Rejects, cast(SUM((Rej_Qty)) as decimal)/ cast(SUM((Acc_Qty))+SUM((Rej_Qty)) as decimal) as Scrap_Percent, (SUM(cast (Rej_Qty as int)) * Part_Cost.Cost) as Total_Cost from Lot_Records INNER JOIN Part_Numbers ON Lot_Records.Part_Id=Part_Numbers.Part_Id INNER JOIN Part_Cost ON Part_Numbers.Part_Number=Part_Cost.Part_Number GROUP BY Part_Numbers.Part_Number,Part_Numbers.Part_Description, Cost ORDER BY Total_Cost DESC