Please Help with table design

[shaneenahs]

I have two tables:
PRICE_LIST
(PK)MetalType
SellPrice
and
CUSTOMER_PRICE_LIST
(PK)CustomerID
(PK)Date
MetalType
BuyPrice
Margin

What I need is to make each row's BuyPrice to be an equation of SellPrice
minus Margin. How do I do this?

 

[Allen Browne]

Do not store the BuyPrice in the table.
Instead, create a query that calculates this when needed.

The query will contain both tables (joined on the MetalType), and you will
type something like this into the Field row in query design:
BuyPrice: [SellPrice] - [Margin]
You can then use this query anywhere you could have used the table, but you
never have to worry about whether the result is wrong or not.

More info:
Calculated fields
at:
http://allenbrowne.com/casu-14.html