Hey, Jim. Create a view for the summation, and it will work like a charm. CREATE VIEW vwCatSum (Category,CostTotal) AS + SELECT Category,SUM(Quantity*CostAtPurchase) + FROM CostAtPrchas + GROUP BY Category
UPDATE SummaryTable + SET LstYrTotalCost = t2.CostTotal + FROM vwCatSum t2, LstYrTotalCost t1 + WHERE t1.Category = t2.Category Jim Limburg <[EMAIL PROTECTED]> wrote: >G-Day all > >I have a two column table that has one column populated with inventory >categories called categories and one that holds lstyrtotalcst.. (Last Years >Total Costs) > >I have another table that has category (based off above table), quantity, and >costatprchas (cost at purchase). This table is items purchased during the year >and the category is picked the cost is put in at the time it was purchase and >of course how many purchased > >I need to update the Last Years Cost with a sumation of the quantity * >costatprchas �for each row. Each rows cost is likely different so I can't just >determine it easily. > >I have thought of developing a double cursor system to tally it up, but it's >not really flowing easily down this road either. > >Is there a good SQL clause like: > >update totalstable set styrtotalcst = (select sum(quantity * costatprchas ) >where category = category.. > >or something similar > >Jim Limburg > > -- Albert Berry Full Time Consultant to PSD Solutions 350 West Hubbard, Suite 210 Chicago, IL 60610 312-828-9253 Ext. 32 __________________________________________________________________ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
