update “Total” set “Total”.“IssueTotal” = (SELECT SUM( “Issue”.“Quantity” ) FROM “Issue” where “Issue”.“ProductName”=“Total”."ProductName"GROUP BY “Issue”.“ProductName”); update “Total” set
“Total”.“RecieptTotal” = SELECT SUM( “Reciept”.“Quantity”) FROM “Reciept”
WHERE “Total”.“ProductName” = “Reciept”.“ProductName”
GROUP BY “Reciept”.“ProductName”; update “Total” set “Total”.“Balance” = SELECT SUM( “Total”.“RecieptTotal” - “Total”.“IssueTotal” ) FROM “Total” GROUP BY “Total”.“ProductName”
This is three different statements. You already have the basis for a macro to execute these (here). Just replace this line:
sSQL = "UPDATE ""Total"" SET ""Total"".""IssueTotal"" =" &_
" (SELECT SUM( ""Issue"".""Quantity"") FROM ""Issue""" &_
" WHERE ""Issue"".""ProductName""=""Total"".""ProductName""" &_
" GROUP BY ""Issue"".""ProductName"")"
with each of your update statements surrounded by "
(quote) marks and double "
around table and field names. You must run each separately. Here is the first:
sSQL = "update ""Total"" set ""Total"".""IssueTotal"" = (SELECT SUM( ""Issue"".""Quantity"" ) FROM ""Issue"" where ""Issue"".""ProductName""=""Total"".""ProductName"" GROUP BY ""Issue"".""ProductName"")"
And execute each individually with:
oStatement.executeUpdate( sSQL )
Thanks to clear about macro formatting…