theory question storing calculated data

Hi,

From reading the newsgroup messages, it seems that most people regard
storing calculated data in a table field is not good design practice.

So I'm not clear how to proceed.

I'm designing a database to use for generating quotes.

There will be lookup tables used to generate the prices for each part
number. I'm imagining that I'll calculate the different prices and
save the calculated total in the quote details table. I don't want to
recalculate the quote if someone needs to see it later. The underlying
prices could change and all the quote details would change, but that
would not be what we sent the customer.

I'm not clear how to design this functionality.

Any suggestions are welcome and appreciated.

Thanks,

Dan

In some cases it is acceptable to store calculated data. But you can have
prices in a table with the As_Of_Date so when you recalulate you can retrieve
the price that was.

"dan dungan" wrote:

Hi,

From reading the newsgroup messages, it seems that most people regard
storing calculated data in a table field is not good design practice.

So I'm not clear how to proceed.

I'm designing a database to use for generating quotes.

There will be lookup tables used to generate the prices for each part
number. I'm imagining that I'll calculate the different prices and
save the calculated total in the quote details table. I don't want to
recalculate the quote if someone needs to see it later. The underlying
prices could change and all the quote details would change, but that
would not be what we sent the customer.

I'm not clear how to design this functionality.

Any suggestions are welcome and appreciated.

Thanks,

Dan

In my opinion, yours is one of the rare exceptions to that rule and you
should store the result.

The difference is that your result is not merely, the result of a
calcualtion, it is a record of what price you told the customer.

Thanks for your reply Fred.

On May 29, 12:48*pm, KARL DEWEY
wrote:
In some cases it is acceptable to store calculated data. *But you can have
prices in a table with the As_Of_Date so when you recalulate you can retrieve
the price that was.

"dan dungan" wrote:
Hi,

From reading the newsgroup messages, it seems that most people regard
storing calculated data in a table field is not good design practice.

So I'm not clear how to proceed.

I'm designing a database to use for generating quotes.

There will be lookup tables used to generate the prices for each part
number. *I'm imagining that I'll calculate the different prices and
save the calculated total in the quote details table. I don't want to
recalculate the quote if someone needs to see it later. The underlying
prices could change and all the quote details would change, but that
would not be what we sent the customer.

I'm not clear how to design this functionality.

Any suggestions are welcome and appreciated.

Thanks,

Dan

Thanks for your reply, Karl.

To make it easy when price of something is involved is to store the actual
price rather than some foreign key. For example, in your case when you enter
parts into the subform based on TblQuoteDetail, for each part enter its
actual price you are quoting. Then any time in the future, the price you
quoted will be readily available.

Steve



"dan dungan" wrote in message
...
Hi,

From reading the newsgroup messages, it seems that most people regard
storing calculated data in a table field is not good design practice.

So I'm not clear how to proceed.

I'm designing a database to use for generating quotes.

There will be lookup tables used to generate the prices for each part
number. I'm imagining that I'll calculate the different prices and
save the calculated total in the quote details table. I don't want to
recalculate the quote if someone needs to see it later. The underlying
prices could change and all the quote details would change, but that
would not be what we sent the customer.

I'm not clear how to design this functionality.

Any suggestions are welcome and appreciated.

Thanks,

Dan

dan dungan wrote:

From reading the newsgroup messages, it seems that most people regard
storing calculated data in a table field is not good design practice.

Correct. Most of the time.

There will be lookup tables used to generate the prices for each part
number. I'm imagining that I'll calculate the different prices and
save the calculated total in the quote details table. I don't want to
recalculate the quote if someone needs to see it later. The underlying
prices could change and all the quote details would change, but that
would not be what we sent the customer.

Now this is a good example of when that rule doesn't work. Or rather
what you are storing is the price at that moment in time. So it
does follow in the rules.

Also what happens if the person doing the quote phones up the supplier
and asks if they can get a good deal due to a large volume and so you
want to put in a slightly reduced price good for just that order?

I'm not clear how to design this functionality.

In the After Update event of the part number combo box on the quote
number screen you would insert the current price in the quote details
table. I would put the price as a hidden field in the combo box and
use that column to simplify logic and keep the number of disk/network
reads down.

Now what I also do is, if the user overrides the price, is have a
second locked field which I set to O (letter Oh). If the user clears
the price field I fetch the price from the hidden field in the combo
box and clear the over ride field.

I will also, usually, put the markup % field on the form as well but
that is computed in the query on which the subform is based.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/