After some further experimentation I've gotten close (or so it seems) but not quite there. My latest attempt at this is shown below. The inner query (starting with Select (D.LineItemTotal) and ending with (Where M1.MDEP = MDEP.MDEP) works perfectly when run by itself and the final where clause is assigned a literal value (i.e., Where M1.MDEP = 'abcd'). This inner query will calculate a correct numeric value for each Purchase Order having an MDEP value matching that in the Where clause.

Now, the final step would be to bracket this inner query with an Update MDEP statement similar to the one shown below, that assigns the sum of the inner query results to the row in the MDEP table with the matching MDEP column. Regardless of how I approach this final step, I run into one error or another.

If anyone has any thoughts on how to approach this I would be most appreciative.

Update MDEP

Set MDEP.POObligations = SUM

(

Select (D.LineItemTotal - M1.PaidToDate) * M2.SplitPercent As POTotal from

(

(select PONumber, MDEP, PaymentPosted as PaidToDate from PO_MX) AS M1

inner join

(select PONumber, cast(PrimarySplit as decimal(5,2))/100 as SplitPercent from PO_MX) AS M2 on M1.PONumber = M2.PONumber

inner join

(select PONumber, SUM(QuantityOrdered * Price) as LineItemTotal from PO_DX group by PONumber) AS D on M2.PONumber = D.PONumber

)

Where M1.MDEP = MDEP.MDEP

)

Where MDEP IN (Select MDEP From PO_MX)