Hello, Friends
I need little help.

I need to update a Master-table (Check) from a Detail-table (Items), but the
problem is
that the detail-amount must a SUM(DetailAmt) for every check in the master
file.

Example:

Master-Table (Check)  -BEFORE
Ck#    CkAmount
101        0.00
102        0.00
103        0.00

I need to UPDATE using SQL CkAmount from file above using the folowing:

Detail-File (Items)
ItemCk#     ItemAmount
101                   10.55
101                    20.00
101                    30.00
102                   200.50
102                   100.50
103                       5.70
103                     10.00

The result after executing SQL must be  -AFTER
Ck#    CkAmount
101         60.55
102        301.00
103          15.70

-----------------------------------------------------------
I was using....this but does not work
UPDATE Check SET CkAmount = SUM(ItemAmount)
FROM Check, Items
WHERE  Ck# = ItemCk#

Thank you very much in advance.

Marcos Romero
MRROM...@EMAIL.COM

This will work for you:

INSERT INTO Check(Ck#,CkAmount)  SELECT  ItemCk#,Sum(ItemAmount) FROM Item
Group by ItemCheckNr

This SQL-statement wil insert new rows into the masterTable with the exact
totals of the detailTable.  But
You will have to remember witch was the last number you already calculated,
because otherwise you will
add all the totals of the deailTable everytime you make an update.
This is why you will have to include a WHERE statement like this:

insert into check(Ck#,CkAmount)  select  ItemCk#,Sum(ItemAmount) from Item
WHERE ITEMCH# > 103
Group by ItemCheckNr

Christophe.

Marcos_Romero heeft geschreven in bericht <3a630fc3$1_1@dnews>...