My thinking so far has been to create the TempTable with all three columns, then insert the item/lot combos with a select disctint statement. Then I tried to update the pieces column by selecting the SUM of the pieces for a given lot number. I have:
CREATE TABLE #TempList( Item nvarchar(20), Lot nvarchar(15), TotalPieces Decimal(9,1) )
INSERT INTO #TempList
SELECT DISTINCT [Item Number], [Lot Number] FROM Flex_MachineActivityData
Update #TempList set TotalPieces = (Select sum(pcs) from Flex_MachineActivityData where Flex_MachineActivityData.[Lot Number] = #TempList.Lot) From #TempList
Select * from #TempList
It compiles fine, then at run-time I get: "Column name or number of supplied values does not match table definition"
Which makes me think the third column was not established. Ideas?