Basic query causing a field to be BLANK ?

[David]

Hi,

I have a table with a field called 'tobuild'
This basically holds a figure based on the result of some other stock
figures in the same table, 'StockQty' & 'MSQ'.
I know you are not supposed to hold a calculated result in a field,
but why ?. It works ok for everything in my system, except for when I
run this query.

MSQ holds the max stock level, StockQty holds the current stock qty and
tobuild holds the value of MSQ minus (-) StockQty.

All tobuild values were initially set as zero.

On a form, tobuild is calculated and set when the StockQty is manually
adjusted.
I also want it to be auto adjusted when orders are placed and stock is
reduced.

I have a query which updates the StockQty fine, and have then tried to
follow on with a query to calculate MSQ-StockQty after the update where
the product name matches

UPDATE stock SET stock.tobuild = "MSQ - StockQty"
WHERE (((stock.StockN ame)=[Forms]![Customer Orders]![OrderLines
Subform].[Form]![SDProdCode]));

The query runs fine, except, for that particular product, tobuild
always ends up as a blank field, and hence causes errors on my asp web
pages. This seems to me the most basic calculation ?

Any clues / answers would be most appreciated.

Thanks.

David

[Larry Linson]

Re: Basic query causing a field to be BLANK ?

"David" <davidgordon@sc ene-double.co.ukwro te

I have a table with a field called 'tobuild'
This basically holds a figure based on the result of some other stock
figures in the same table, 'StockQty' & 'MSQ'.
I know you are not supposed to hold a calculated result in a field,
but why ?.

It is NOT true that you are NEVER to hold a calculated result in a field. It
is true that it is unadvisable to hold a result if that result can be
re-calculated when needed from values available at the time of need. And
there is a very good reason: sooner or later, sometime, somehow, during
maintenance or enhancement, you will end up with a mismatch in the
calculated value and the factors used to calculate it, and trying to figure
what went wrong, where will "drive you to distraction".

However, a price calculation in an Order Detail Record based on a Price in a
Product Record likely should be stored, because the price at the time of
order is guaranteed to the customer, but the Product Record may be changed
prior to the time you are reviewing the orders or analyzing historical data,
so recalculating using the current Price would not yield the same result.

If you read this, and your response is, "But you didn't tell me how to
correct my problem, anyway, without changing the improper design.", you are
correct.

Larry Linson
Microsoft Access MVP