How do you mulitply in a field?
Jamie
I'm curious why the same admonition about rarely needing to store calculated
values in a table would have to be applied to views, i.e., your "virtual
tables"? Do you have a reference so I can read up more on this?
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Jamie Collins" wrote in message
oups.com...
On 15 Aug, 22:05, fredg wrote:
I created a table with two fields named "projectestimate" and
"inspectionfee".
The inspection fee is 5% of the project estimate. I want the
"inspectionfee"
field to automically calculate the amount.
How do I do that?
In a table? You don't.
Access tables are for storing data, not for data manipulation or
display.
you can do the calculation directly on a form or report, using an
unbound text control:
= [ProjectEstimate] * [InspectionRate]
the resulting value should not be saved in any table.
In this simple case I agree: there can be no good reason for storing
the result.
If you store the [ProjectEstimate] and [InspectionRate] it's easy
enough to calculate the actual fee in a Query:
InspectionFee:[ProjectEstimate] * [InspectionRate]
Not sure I agree anymore. Did you mean to say 'query' (lowercase)?
If you saved your query with the calculated column as a stored Query
(Title Case) object then this would become a virtual table. You would
seem to be saying that denormalization in a base table is wrong but
fine in a virtual table. That sounds contradictory.
For me, this is one of the differences between a VIEW and a PROCEDURE
i.e. the resultset of a PROC doesn't need to be normalized. The
trouble is, how do you get the Access/Jet engine to see your PROC as a
PROC e.g. this in Northwind (ANSI-92 Query Mode SQL syntax):
CREATE PROCEDURE GetOrderProcessIntervals
AS
SELECT OrderID, OrderDate, ShippedDate,
DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days
FROM Orders;
actually creates a VIEW! i.e. although it can be EXECUTED:
EXECUTE GetOrderProcessIntervals;
it can also be queried as a table:
SELECT *
FROM GetOrderProcessIntervals;
and we now have a denormalized table.
The only workaround I can think of is to supply a useless parameter
value e.g.
CREATE PROCEDURE GetOrderProcessIntervals
(
this_not_used INTEGER = 0
)
AS
SELECT OrderID, OrderDate, ShippedDate,
DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days
FROM Orders;
Now
SELECT *
FROM GetOrderProcessIntervals;
fails with a 'missing parameter' error.
So the choice seems to be to ask users to ignore daft parameter or be
a little less strict on normalization rules.
Jamie.
--
|