theory question re: storing calculated data

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 <KARLDE... DeleteThis @discussions.microsoft.com>
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
santus.TakeThisOut@penn.com


"dan dungan" <stagerobber.TakeThisOut@yahoo.com> wrote in message
news:30978981-490c-4ea9-94d9-a5f32e6f98c8@g19g2000yql.googlegroups.com...
> 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 <stagerobber.RemoveThis@yahoo.com> 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/