On fly (I don't have Access available today):
Create a saved query e.g. aProductPrices like
After that you can create a simple update query to update tblProducts with product_price from qProductPrices.Code:SELECT product_id, SUM((Nz(price,0)*Nz(quantity,0)) As product_price GROUP BY product_id
I try this way:
but I get a message: "Operation must use an updateable query"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
What is wrong?
---
Sorry for my English
ArviLaanemets, I did as you suggested in your first post (I think so).
I created a query q1:
Then I created update query:Code:SELECT product_id, SUM(Nz(price,0)*Nz(quantity,0)) AS product_price FROM tblComponents GROUP BY product_id;
I still get the message: Operation must use an updateable query.Code:UPDATE tblProducts p SET p.product_price = (select product_price from q1 where q1.product_id = p.product_id)
I also read the article you submitted, but I can not apply these tips in practice.
---
Sorry for my English
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!
Forgot to add the file!
Thank you for the explanations and examples!