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.

--