Calculated fiels in a table

[ewentms]

I have a table with sku, date, minimum price and markup, another field is
selling price.
I would like to get the selling price to reflect Minimum Price * Markup
automatically.
I could do it in SQLServer, but I guess it is different in Access??
TIA,
Ron

 

[Klatuu]

Not in the table field, do it in a query, on a form, or in a report.
I would not store calculated data in any database.

 

[Jeff Boyce]

Ron

I agree with Dave ... if you are working in an Access database, trying to
store calculated values in your tables will put your data integrity at risk.

Here's a scenario (and I recommend AGAINST IT!):

* You add a calculated field to an Access table, based on two other fields
* Someone fixes one of the other fields (data entry error, sorry!)
* Your calculated field is now out of synch, unless you create a routine
that runs every time after any of the component parts change, AND after a
'manual' change in the value of the calculated field.

If you use a query to do the calculation, it will always reflect the current
values in the other fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve]

Ron,

I agree with both Dave and Jeff. You should have tables that look like:
TblProduct
ProductID
ProductDescription
SKU

TblProductPriceMarkup
ProductPriceMarkupID
ProductID
AsOfDate
MinimumPrice
Markup

TblCustomer
CustomerID
CustomerName
<<Other customer contact fields>>

TblOrder
OrderID
CustomerID
OrderDate
<<Other Order related fields>>

TblOrderDetail
OrderDetailID
OrderID
PriductID
Quantity
ProductPriceMarkupID

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Jamie Collins]

if you are working in an Access database, trying to
store calculated values in your tables will put your data integrity at risk.

Here's a scenario (and I recommend AGAINST IT!):

* You add a calculated field to an Access table, based on two other fields
* Someone fixes one of the other fields (data entry error, sorry!)
* Your calculated field is now out of synch, unless you create a routine
that runs every time after any of the component parts change, AND after a
'manual' change in the value of the calculated field.

In summary, you seem to be saying that one shouldn't store calculated
values in a SQL DBMS but if you do you should do it in such a way that
the values don't get out of sync, in which case I concur. With Access/
Jet, such a "routine" could be an engine level Validation Rule or
CHECK constraint. However, in the OP's case I can't see that there is
any good reason for doing so.

Jamie.

--

 

[Jamie Collins]

I would not store calculated data in any database.

I wager you would under certain circumstances e.g. when the
calculation is external to the database, when the calculation takes
several hours to complete, etc.

Jamie.

--

 

[Jamie Collins]

I wager you would under certain circumstances

A couple of real life examples here where storing a calculated state
may be justified:

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/8f7dcb9186d17b1e

Jamie.

--

 

[Jeff Boyce]

Jamie

There may be a qualitative difference between the situation described by the
OP and a 10M row transaction log. I can see (and everyday, I happen to use)
a "summary" table that makes querying for a particular time interval (or
other "cube-oriented", data warehouse solution). I'm not sure that's in the
same domain.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jamie Collins]

There may be a qualitative difference between the situation described by the
OP and a 10M row transaction log. I can see (and everyday, I happen to use)
a "summary" table that makes querying for a particular time interval (or
other "cube-oriented", data warehouse solution). I'm not sure that's in the
same domain.

FWIW I wasn't replying to the OP. Rather, I was responding to David
'Klatuu' Hargis Access MVP's assertion, "I would not store calculated
data in any database."

Jamie.

--

 

[Jamie Collins]

FWIW I wasn't replying to the OP. Rather, I was responding to David
'Klatuu' Hargis Access MVP's assertion, "I would not store calculated
data in any database."

Upon checking, I see that I said, "in the OP's case I can't see that
there is any good reason for doing so."

Jamie.

--

 

[Dale Fye]

Jamie,

I could see where, in a SQL Server database, where you can create triggers
to maintain data integrity, you might want to do this, but with Access, there
are just too many ways around the data entry process to ensure that when one
of the fields used in your calculated field gets updated that it doesn't
corrupt your data.

Dale

 

[Jamie Collins]

I could see where, in a SQL Server database, where you can create triggers
to maintain data integrity, you might want to do this, but with Access, there
are just too many ways around the data entry process to ensure that when one
of the fields used in your calculated field gets updated that it doesn't
corrupt your data.

I don't think that is correct. If an engine-level constraint could be
circumvented by any means (short of dropping it) then it would be a
bug in the engine; I know of no such bug in the Access/Jet engine.
I've heard of duplicated values in an autonumber PRIMARY KEY column
due to file corruption but I would wager serious money that the
PRIMARY KEY had been lost in the process. I know of CHECK constraints
being checked too early but that could be considered erring on the
side of caution <g>.

Take a very simple example:

CREATE TABLE MyCalcTable
(
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
CHECK (col3 = col1 + col2)
);

(One could use a Validation Rule in place of that CHECK constraint.)
Can you suggest any "ways around the data entry process" that would
result in col3 being anything other than the sum of col1 and col2? I
cannot.

FWIW (slightly OT) I don't see anything special about implementing a
constraint as a trigger when compared with a CHECK constraint. In SQL
Server, the optimizer can take account of CHECK constraints but has no
knowledge of triggers so they can be slow as a result. Triggers are
reactive and procedural, constraints are proactive and declarative. In
a trigger you have to explicitly raise errors to the callee, rollback
transactions, etc whereas with a constraint all this *housekeeping* is
done for me by the DBMS. Most of the AFTER triggers I write (INSTEAD
OF triggers are a different animal e.g. most often used to update an
otherwise un-updatable VIEW) I wouldn't need if SQL Server supported
full SQL-92 as regards CHECK constraints -- i.e. allow subqueries (as
Access/Jet does) and be deferrable (as Oracle does) -- and ASSERTIONs.

Jamie.

--

 

[Dale Fye]

Jamie,

OK, triggers are reactive, DUHHHHHHH! If I change Col1 and Col3 depends on
Col1, I should update Col3. This is preferable to a check constraint, that
makes no changes in the value of Col3, but generates an error that prevents
you from changing Col1 or Col2. I can see where this might be fine in an
unbound form, where you write all the values to their appropriate fields in
a single insert or update statement, but in a bound form, when I change the
value of Col1, it attempts to change that value in the table (which would
violate the check constraint) and therefore should generate an error.

I get an error (Invalid SQL Syntax - cannot use multiple columns in a
column-level check constraint) within Access 2003. How would you implement
this validation rule within Access, since we are talking about Access in
this group, not SQL SERVER.

Dale

 

[Jamie Collins]

OK, triggers are reactive, DUHHHHHHH!

I was comparing and contrasting to constraints, so why the
"DUHHHHHHH!" here? I also said 'constraints are declarative', another
blatant statement of fact, but that didn't get a "DUHHHHHHH!" from
you. Perhaps we're suffering a culture clash

In case the point was lost, the SQL language is declarative and set
based whereas triggers are reactive and procedural, hence not a great
fit on that basis.

If I change Col1 and Col3 depends on
Col1, I should update Col3.

in a bound form, when I change the
value of Col1, it attempts to change that value in the table (which would
violate the check constraint) and therefore should generate an error.

The constraint is to stop bad data from reaching the base table so job
done. Your job as front end coder is to either trap the bad data
before it gets sent to the DBMS or handle the error the DBMS
generates. Sounds like your (hypothetical) form is writing data to the
DBMS too early.

Sure, I'd like triggers in the Access/Jet engine but all we have are
constraints and Validation Rules

I get an error (Invalid SQL Syntax - cannot use multiple columns in a
column-level check constraint) within Access 2003. How would you implement
this validation rule within Access

It's a quick example in vanilla standard SQL. Do you need to execute
it directly as posted to understand it? If so, you could execute it in
the Access user interface via ANSI-92 Query Mode (http://
office.microsoft.com/en-gb/access/HP030704831033.aspx); ensure you are
putting a comma between the column definition and the constraint
definition in the CREATE TABLE statement because all CHECK constraints
in Access/Jet are table level (well you can code them to refer to the
'current' row but I wouldn't recommend for too many reasons to post
here). Or you could take my aforementioned advice, "use a Validation
Rule in place of that CHECK constraint," the implementation of which
would be a record (row) level Validation Rule (a.k.a. Table Validation
Rule) and would probably look like this:

([col3]) = (([col1]) + ([col2]))

Now that I have suggested two ways of implementing this simple example
in Access, are you now in a position to confirm whether there are or
are not any "ways around the data entry process" short of dropping the
CHECK/Validation Rule to make col3 become anything other than col1 +
col2? TIA.

we are talking about Access in
this group, not SQL SERVER.

Both the OP and yourself mentioned SQL Server in this thread before I
did so why pick on me for replying?

Jamie.

--