sql update sum
-
You're posting SQL. Does the SQL work in Access ? If not, that is your issue. if so, then your issue is with the code you're not posting, the code that is appropriate for this forum. You know, in VB.
Christian Graus No longer a Microsoft MVP, but still happy to answer your questions.
-
In MSSQL, the syntax would be UPDATE t1 SET f1 = SUM(t2.f1) FROM t1 INNER JOIN t2 ON t1.code = t2.code I assume it would be the same in Access. The key here is that the column receiving the update is not aliased, and any tables used in the update need to have their own references in the FROM statement. Don't use old style SQL Join syntax (from t1, t2 where t1.code=t2.code) if you can help it. Its difficult to follow.
-
In MSSQL, the syntax would be UPDATE t1 SET f1 = SUM(t2.f1) FROM t1 INNER JOIN t2 ON t1.code = t2.code I assume it would be the same in Access. The key here is that the column receiving the update is not aliased, and any tables used in the update need to have their own references in the FROM statement. Don't use old style SQL Join syntax (from t1, t2 where t1.code=t2.code) if you can help it. Its difficult to follow.
ty for the reply but i get this error Syntax error (missing operator) in query expression 'SUM(Accessory_Table.Qty * Accessory_Table.Cost) FROM Stock_Table INNER JOIN Accessory_Table ON Stock_Table.StkCode = Accessory_Table.StkCode'. this is my actual code "UPDATE Stock_Table " & _ "SET StkCost = SUM(Accessory_Table.Qty * Accessory_Table.Cost) " & _ "FROM Stock_Table " & _ "INNER JOIN Accessory_Table " & _ "ON Stock_Table.StkCode = Accessory_Table.StkCode" maybe UPDATE and SUM doesnt really work together in access?