Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Question 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?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Try this instead:

    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.

    Let me know if this solves the problem.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    matthew, i'm not sure that will work

    you have a derived table called p_Query --

    [SELECT SUM(p_Sales.Qty*p_Sales.Price) AS extPrice FROM p_Sales GROUP BY p_Sales.CstActNbr, p_Sales.PrdNbr;]. AS p_Query

    but then you make reference to a non-existent column --

    WHERE p_Query.Posted >= tmpSalesAnalysis.dtStartDate AND p_Query.Posted <= tmpSalesAnalysis.dtEndDate


    seems to me that the calculation of total sales grouped by account and product is just not possible without the relevant date range

    it raises the (unlikely) scenario that different account numbers in the tmpSalesAnalysis table might have different date ranges

    if there's supposed to be a consistent date range for the entire operation, then those values should really be supplied as literal parameters and not as columns to be matched
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2003
    Posts
    5
    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*

    Thanks for the posts!

  5. #5
    Join Date
    Feb 2004
    Posts
    137
    Sorry about that. Long day the other day and my brain was numb.

    In Access, you can use the Domain Aggregate function DSum() to do this, like so:

    UPDATE tmpSalesAnalysis SET tmpSalesAnalysis.GrossSalesAmt = DSum("[Qty]*[Price]","p_Sales","[PrdNbr] = " & [Group2Value] & " AND [CstActNbr] = " & [Group1Value] & " AND [Posted] >= #" & [dtStartDate] & "# AND [Posted] <= #" & [dtEndDate] & "#");

    Of course, if [Group1Value] and [Group2Value] are text fields and not numeric, it would have to be:

    UPDATE tmpSalesAnalysis SET tmpSalesAnalysis.GrossSalesAmt = DSum("[Qty]*[Price]","p_Sales","[PrdNbr] = '" & [Group2Value] & "' AND [CstActNbr] = '" & [Group1Value] & "' AND [Posted] >= #" & [dtStartDate] & "# AND [Posted] <= #" & [dtEndDate] & "#");

    Note, in the second instance, the single and double quotes carefully. If you cut and paste the text straight, there should be no problem. Let me know if this works for you.

  6. #6
    Join Date
    Oct 2003
    Posts
    5

    Cool

    Yes! This is exactly what I needed. Reports that were taking 10-15 minutes to run are now finishing in under 30 seconds.

    Thanks -- you rock!

  7. #7
    Join Date
    Jul 2010
    Posts
    3

    Almost same problem

    Hi,

    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)

    Please help

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you please try to rephrase your question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2010
    Posts
    3

    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:

    Code:
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE CopyProductBatch AS p 
    LEFT OUTER
      JOIN ( SELECT productbatchcode
                  , SUM(IIF(type = 'p',qty,0)) AS p_total 
                  , SUM(IIF(type = 's',qty,0)) AS s_total 
               FROM Inventory 
             GROUP 
                 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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2010
    Posts
    3
    Thanks r937, but MS Access says "operation must be an updateable query"

    I have tried lots of other queries also including using temporary tables etc. Nothing doing...

    Heres the link to the mdb file - www.prakhargoel.com/projects/pos/POS.mdb

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2011
    Posts
    7

    Extra bracket error

    Hi

    am trying to update a table by from calculated sums but am getting an error of an extra bracket i.e. extra )

    Below is my source
    Code:
    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].[Comment]= "&[DimensionsTotal].[ DimComment]& " AND [Dimensions].[ProjectID]= "&[DimensionsTotal].[ProjectID]& " AND [Dimensions].[ProjectID]= "&[@project_id]& " AND [Dimensions].[Comment]= "&[@comment]& ) ;
    what am I over lookin...

  14. #14
    Join Date
    Jun 2011
    Posts
    7
    I seem to have resolved the bracket error however now on update I have a different problem. This is my source

    Code:
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •