Have tried several things… but must be 'lost on the concept…'
Here are the two tables:
Table = Task
Task.Rec = XXXXXX
Task.ClientRec = XXX
Table = TaskDtls
TaskDtls.Rec = XXXXXX
TaskDtls.ClientRec = XXX
There are many detail records associated with the main task record.
Task.ClientRec = ‘A1a1’
TaskDtls.Rec TaskDtls.ClientRec TaskDtls.iSeq TaskDtls.DetailExp TaskDtls.PaidExp
’AAA’ A1a1 1 2.00 1.00
’AAA’ A1a1 2 4.00 2.00
’AAA’ A1a1 3 9.00 3.00
So at the end of the query the
Task.TotalPaidExpense = sum(TskDtls.PaidExp)
I tried something similar
SET Task.TotalDetailExpense = S1.ExpAmt, TotalPaidExpense = S1.PdAmt
INNER JOIN (SELECT SUM(DetailExp) as ExpAmt, SUM(.PaidExp) as PdAmt
FROM TaskDtls WHERE TaskDtl.Rec ='AAA' AND TaskDtl.ClientRec = 'A1a1'
GROUP BY REC) as S1
But that didn't work.. tried other combinations ....
One MAIN point, is the update has to be specific a certain set
of record (i.e. TaskDtl.Rec ='AAA' AND TaskDtl.ClientRec = 'A1a1')
I don't want to have the whole table updated.
Thanks in advance for your advice / directions