Help Me With This Access Query
- by yae
I have 2 tables: "products" and "pieces"
PRODUCTS
idProd
product
price
PIECES
id
idProdMain
idProdChild
quant
idProdMain and idProdChild are related with the table: "products".
Other considerations is that 1 product can have some pieces and 1 product can be a piece.
Price product equal a sum of quantity * price of all their pieces.
"Products" table contains all products (p
EXAMPLE:
TABLE PRODUCTS (idProd - product - price)
1 - Computer - 300€
2 - Hard Disk - 100€
3 - Memory - 50€
4 - Main Board - 100€
5 - Software - 50€
6 - CDroms 100 un. - 30€
TABLE PIECES (id - idProdMain - idProdChild - Quant.)
1 - 1 - 2 - 1
2 - 1 - 3 - 2
3 - 1 - 4 - 1
WHAT I NEED?
I need update the price of the main product when the price of the product child (piece) is changed.
Following the previous example, if I change the price of this product "memory" (is a piece too) to 60€, then product "Computer" will must change his price to 320€
How I can do it using queries?
Already I have tried this to obatin the price of the main product, but not runs. This query not returns any value:
SELECT Sum(products.price*pieces.quant) AS Expr1
FROM products LEFT JOIN pieces ON (products.idProd = pieces.idProdChild) AND (products.idProd = pieces.idProdChild) AND (products.idProd = pieces.idProdMain)
WHERE (((pieces.idProdMain)=5));
MORE INFO
The table "products" contains all the products to sell that it is in the shop.
The table "pieces" is to take a control of the compound products. To know those who are the products children. For example of compound product: computers. This product is composed by other products (motherboard, hard disk, memory, cpu, etc.)
