A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

product total



 
 
Thread Tools Display Modes
  #1  
Old November 19th, 2004, 04:29 PM
Abrm
external usenet poster
 
Posts: n/a
Default product total


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.



  #2  
Old November 28th, 2004, 10:50 PM
Johan Koopmans
external usenet poster
 
Posts: n/a
Default

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




"Abrm" schreef in bericht
...

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.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
bypassing some of the parameters in a parameter query Lorian Running & Setting Up Queries 27 September 23rd, 2004 06:23 PM
Importing into Excel from a Text file Greg Gates General Discussion 1 September 7th, 2004 01:36 PM
can't reinstall office xp pro after deleting from windows xp syste dana Setup, Installing & Configuration 1 August 5th, 2004 06:32 PM
can't reinstall office xp pro after deleting from windows xp syste mkskyflyer Setup, Installing & Configuration 1 August 5th, 2004 05:39 AM
Cummaltive/Summarised Item total on a different product vlookup, depending on the description on the operation John Colling Worksheet Functions 2 February 21st, 2004 04:44 PM


All times are GMT +1. The time now is 07:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.