Results 1 to 8 of 8
  1. #1
    andrews is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2019
    Posts
    7

    Update query

    Hi,

    I have a problem with writing a query to update the product_price field with the sum of components values (price * quantity)
    Click image for larger version. 

Name:	tbl_relations.jpg 
Views:	18 
Size:	17.4 KB 
ID:	38291

    andrews

    ---
    Sorry for my English

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    On fly (I don't have Access available today):

    Create a saved query e.g. aProductPrices like
    Code:
    SELECT product_id, SUM((Nz(price,0)*Nz(quantity,0)) As product_price GROUP BY product_id
    After that you can create a simple update query to update tblProducts with product_price from qProductPrices.

  3. #3
    andrews is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2019
    Posts
    7
    I try this way:

    Code:
    Update tblProducts p inner join (SELECT product_id, SUM(Nz(price,0)*Nz(quantity,0)) As product_price from tblComponents GROUP BY product_id) qPP on p.product_id = qPP.product_id
    SET p.product_price = qPP.product_price
    but I get a message: "Operation must use an updateable query"

    What is wrong?


    ---
    Sorry for my English

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673

  5. #5
    andrews is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2019
    Posts
    7
    ArviLaanemets, I did as you suggested in your first post (I think so).

    I created a query q1:


    Code:
    SELECT product_id, SUM(Nz(price,0)*Nz(quantity,0)) AS product_price
    FROM tblComponents
    GROUP BY product_id;
    Then I created update query:


    Code:
    UPDATE tblProducts p SET p.product_price = (select product_price from q1 where q1.product_id = p.product_id)
    I still get the message: Operation must use an updateable query.

    I also read the article you submitted, but I can not apply these tips in practice.



    ---
    Sorry for my English

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    In attached workbook is an example how to do what you asked.

    Some advice though.
    For calculating such prices, monetary amounts for invoices, etc. are 2 possible scenarios.
    1. Component prices are on-today. summary price is calculated at moment when the document (price offer, invoice or whatever) is closed (no changes are allowed after that). All reports made later are based on saved monetary amounts, and the price table is never used for this product/invoice/whatever again. From this logically follows, that this calculation is called from some event (e.g. user clicks on button, or selects certain status for record in form). As this will be a single-row update, domain functions like DLookup(), DSum() or DCount() may be used in calculation (in queries those functions must be used with caution, as they are slow)
    2. The history of components prices is stored (e.g. you have a table like tblComponentPrices: ComponentPriceID, ComponentID, ComponentPrice, ValidFrom). Summary price is never stored into table, it is calculated when needed.

    You current data structure assumes, detail prices will never change!

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Forgot to add the file!
    Attached Files Attached Files

  8. #8
    andrews is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2019
    Posts
    7
    Thank you for the explanations and examples!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-11-2018, 11:44 AM
  2. Replies: 2
    Last Post: 08-03-2017, 04:58 AM
  3. Replies: 1
    Last Post: 10-27-2016, 12:14 PM
  4. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  5. Replies: 2
    Last Post: 08-30-2012, 07:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums