Unanswered: Updating one table with SUM of field from another table
I'm used to MS SQL Server and can't seem to find a way to do this in MS Access (The SQL that works in SQL Server won't work in Access).
I'm trying to update a field in one table with the Sum of matching records from another table. I've tried several approaches and keep running into errors like "must use updateable query" and "you tried to execute a query that does not include the specified expression GrossSalesAmt as part of an aggregate function".
Here's one example query that I've tried but doesn't work (it's giving me the second error message from the above paragraph):
UPDATE tmpSalesAnalysis INNER JOIN p_Sales ON (tmpSalesAnalysis.Group2Value = p_Sales.PrdNbr) AND (tmpSalesAnalysis.Group1Value = p_Sales.CstActNbr) SET tmpSalesAnalysis.GrossSalesAmt = Sum([p_Sales].[Qty]*[p_Sales].[Price])
WHERE (((p_Sales.Posted)>=[tmpSalesAnalysis].[dtStartDate]) AND ((p_Sales.Posted)<=[tmpSalesAnalysis].[dtEndDate]));
Apparently subqueries don't work either. When I try something like:
UPDATE tmpSalesAnalysis SET GrossSalesAmt = (
SELECT SUM(p_Sales.Qty*p_Sales.Price) FROM p_Sales
WHERE p_Sales.CstActNbr = tmpSalesAnalysis.Group1Value AND p_Sales.PrdNbr = tmpSalesAnalysis.Group2Value
AND p_Sales.Posted >= tmpSalesAnalysis.dtStartDate AND p_Sales.Posted <= tmpSalesAnalysis.dtEndDate
then I get the "must use updateable query" error. Any idea how I can get this to work?
UPDATE tmpSalesAnalysis INNER JOIN [SELECT SUM(p_Sales.Qty*p_Sales.Price) AS extPrice FROM p_Sales GROUP BY p_Sales.CstActNbr, p_Sales.PrdNbr;]. AS p_Query ON tmpSalesAnalysis.Group1Value=p_Query.CstActNbr AND tmpSalesAnalysis.Group2Value=p_Query.PrdNbr SET GrossSalesAmt=extPrice WHERE p_Query.Posted >= tmpSalesAnalysis.dtStartDate AND p_Query.Posted <= tmpSalesAnalysis.dtEndDate;
I've run into the same problem. SQL Server doesn't support updatable inner joins, but Access SQL does. Likewise, Access SQL doesn't support updatable subquery scenarios, but SQL Server does.
r937 is right -- even without the date range, the query above still references p_Query.CstActNbr and p_Query.PrdNbr, which aren't part of the SELECT (the only selected column is extPrice). Trying to run it in Access, Access prompts for p_Query.CstActNbr / p_Query.PrdNbr. If I add CstActNbr / PrdNbr to the SELECT part of p_Query, Access gives the "must use updateable query" error again.
The query is being used to drive a Crystal Report (via VB .NET) that analyzes sales for multiple date ranges. Therefore, the tmpSalesAnalysis table has multiple rows for the same account number with different date ranges. The p_Sales table has all the master "sales" data, and the tmpSalesAnalysis table is trying to sum up the sales for different accounts / date ranges.
Currently, I'm just looping over all the records in VB and then selecting the SUM for each row individually (passing in the CstActNbr, PrdNbr, and date range as static parameters). However, this approach has performance problems, since the tmpSalesAnalysis table can have up to 100,000 rows in it (depending on the report options selected). I was hoping I could eliminate the need for looping and write a query that would update the SUM with a single query.
Actually, if I could get a query to work using static values for the date ranges, I could loop over each distinct date range (10-20 max) and update each batch of rows with the same date range at once. However, I can't seem to get that to work either...
The posts have given me a few new ideas to try, but so far nothing has worked yet. *arg*
I have to update tempqty of all available products in the productlist. For this I have to calculate the difference of the Sums of each product purchased and sold.
I am writing this query for the same in MS access but is not working:
UPDATE CopyProductBatch AS p SET p.tempqty =
(SELECT (a.total - b.total) FROM
(SELECT SUM(qty) AS total FROM Inventory AS i, CopyProductBatch AS p WHERE type = 'p' AND i.productbatchcode = p.productbatchcode) a,
(SELECT SUM(qty) AS total FROM Inventory AS i, CopyProductBatch AS p WHERE type = 's' AND i.productbatchcode = p.productbatchcode) b)
Update rows with the sums of fields from other table
I have two tables: CopyProductBatch and Inventory
CopyProductBatch contains all details about different products like: item code, rate etc and a field- tempqty. tempqty simply is the remaining stock available of that product in the Inventory. The formula to calculate tempqty for a product is- Difference of( (Sum all purchases of that product from wholesaler) - (Sum all retail sales of that product))
I just need to map this query in MS Access for all products. I can do this by iterating over all products and calculating their tempqty individually or frame a query such that all tempqtys are updated in a single shot.
The MySQL query for such a scenario is:
UPDATE CopyProductBatch AS p
LEFT JOIN (SELECT productbatchcode, SUM(qty) AS total FROM Inventory WHERE type = 'p' GROUP BY productbatchcode) AS a USING (productbatchcode)
LEFT JOIN (SELECT productbatchcode, SUM(qty) AS total FROM Inventory WHERE type = 's' GROUP BY productbatchcode) AS b USING (productbatchcode)
SET p.tempqty = IFNULL(a.total,0) - IFNULL(b.total,0)
But this query doesnt work in MS Access. Please suggest equivalent for MS Access.
UPDATE CopyProductBatch AS p
JOIN ( SELECT productbatchcode
, SUM(IIF(type = 'p',qty,0)) AS p_total
, SUM(IIF(type = 's',qty,0)) AS s_total
BY productbatchcode ) AS a
ON a.productbatchcode = p.productbatchcode
SET p.tempqty = IIF(p_total IS NULL,0,p_total) -
IIF(p_total IS NULL,0,s_total)
You cannot use SQL aggregate functions in an UPDATE query in Access, period. There is no way you can do it. As such, you have to either cache the data in a table, use a domain aggregate (DSUM etc) or write your own VBA UDF to aggregate the data.
I seem to have resolved the bracket error however now on update I have a different problem. This is my source
PARAMETERS [@project_id] Text ( 255 ), [@comment] Text ( 255 );
UPDATE DimensionsTotal INNER JOIN Dimensions ON DimensionsTotal.[DimComment]=Dimensions.Comment SET DimensionsTotal.Subtotal = DSum(("[Timsing]*[Dimension]"),"[Dimensions]", ((Dimensions.ProjectID)=[@project_id]) And ((Dimensions.Comment)=[@comment]))
WHERE ((Dimensions.ProjectID)=[@project_id]) And ((Dimensions.Comment)=[@comment]);
However once it runs it returns a sum for all rows including those that do not much the set parameters. How should this be written?