product total

[Abrm]

hi,

I am designing a calculation program. It has 2 tables, calc_header, calc_part
The calc_header has many parts. and the product_price
The calc_part has a sales price.

for creating a product_price, I just calculate all the calc_part!sales price.
Know the I have a sales price. This is different than the sum of
calc_part!sales price

If put it in the product_price, the sum isn't correct.

How can I calcuclate all the different calc_part!sales price? so it works at
two sides.

 

[Johan Koopmans]

Probably your product is made of more parts.
Eg product A consists of part X and part Z
Product B consists of part D and E and G

Factually in a more analytical way a product has a relationship with itself
on entitity level called a recursive relationship, a part can be seen as a
product.
You tables seem to match the recursive relationship quite a bit as you have
calc_header which should consist of:
calc_header
columns: product_ID , part_ID
Your product price is redundant within this table so please make a special
table called 'product' with the 'product price' and delete the column price
from calc_header.
If you leave product price within calc header, and if you produce a sum this
will be different from the parts sum as your calc_header calculates the same
price
as many time as it will find parts. This will lead to nonsense data.

The table calc_header could better be called partperproduct and leave the
price out of this table

Correct salesprice will be shown when using this:

SELECT calc_header.productId, Sum(calc_part.price) AS Salesprice
FROM calc_header INNER JOIN calc_part ON calc_header.partid =
calc_part.partId
GROUP BY calc_header.productId