Have tried several things.. but must be 'lost on the concept...'
Here are the two tables:
Table = Task
Task.Rec = XXXXXX
Task.ClientRec = XXX
TotalDetailExpense money
TotalPaidExpense money
Table = TaskDtls
TaskDtls.Rec = XXXXXX
TaskDtls.ClientRec = XXX
TaskDtls.iSeq int
DetailExp money
PaidExp money
There are many detail records associated with the main task record.
Example
Task.Rec ='AAA'
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.TotalDetailExpense =sum(TskDtls.DetailExp)
Task.TotalPaidExpense = sum(TskDtls.PaidExp)
I tried something similar
UPDATE Task
SET Task.TotalDetailExpense = S1.ExpAmt, TotalPaidExpense = S1.PdAmt
FROM Task
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