Help Me With This MS-Access Query

Question

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 obtain 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.)

Answer 1

You have a duplicate join:

(products.idProd = pieces.idProdChild) AND (products.idProd = pieces.idProdChild)

and the other join should most likely be an OR:

LEFT JOIN pieces ON (products.idProd = pieces.idProdChild) OR (products.idProd = pieces.idProdMain)

from what I can tell by the table structure. Not sure what "vinculated" means exactly, I'm guessing "releated". If that is the case, I'm not sure how you're relating both idProductMain and idProductChild to the Product Table. Maybe some samples of the data would help.

EDIT:

Ok, looks like the join is backwards

LEFT JOIN pieces ON (products.idProdChild = pieces.idProd) OR (products.idProdMain = pieces.idProd)

I think that will get you most of the way there.