Calculated Field in Access Table

[Guest]

I have a table with 5 fields, they are start date (data type = Date/Time);
purchases, years, cost of sales and profit (all have data type = number)
within this table I need to create a calculated field that will do the
following: Calculate the Years of doing business with each customer by
subtracting the Start Date from 8/14/2003. Use 365.25 for the number of days
in a year. Use an Integer to represent the number of complete years. Donot
include partialyears and do not round up. Any decimal places sould be
truncated.

I can do this creating a query, but can not figure out how to do it within
the table.

 

[Douglas J. Steele]

You can't (nor, according to relational database theory, should you).

Using a query is the appropriate way to do this. However, I'd suggest that
there are better ways to calculate the value, such as:

=DateDiff("yyyy", [Start Date], #8/14/2003#) - _
IIf("0814" < Format([Start Date], "mmdd"), 1, 0)

See http://www.mvps.org/access/datetime/date0001.htm for other
possibilities.

 

[Guest]

you can do this with an update query but if it is a
calculated field, it is usually not recommended to add
this to the table. usually(and recommended by MS and MVPs)
the fields that can be calculated are left out of the
tables and when needed for a report or form display, the
control source for the report control or form control is
the formula that begats the product of the calculation.
this is done to same disk space. general rule - never
store in a table data that can be calculated. this is
looked on as redundant data.
good luck.

 

[Larry Daugherty]

Hi connerla,

You can't do what you are seeking to do within a table. You can do it using
queries but you should not. To do so would violate the relational rule to
not store data in a table that is the result of performing calculations on
fields in the same record.

The accepted rule is that you'll calculate and produce those results each
time you need them.

Review the normalization rules and always observe them.

Most people coming to Access have already had lots of Excel experience and
intuitively expect to find Excel functionality within Access. They are each
excellent models within their own domain. Neither is a flawed version of
the other.

HTH

 

[onedaywhen]

You can't (nor, according to relational database theory, should you).

The following article discusses this topic at some length:

http://www.dbazine.com/celko4.html

"You are not supposed to put a calculated column in a table in a pure
SQL database. And as the guardian of pure SQL, I should oppose this
practice. Too bad the real world is not as nice as the theoretical
world..."

Jamie.

--

 

[Tom Wickerath]

Anonymous -

this is done to same (save) disk space.

Actually, there is a much more important reason than simply saving disk space. Storing a result
that can be calculated violates both 2NF and 3NF (second and third normal forms) for database
design. Doing so can lead to inconsistent results.

You might want to download a document that Michael Hernandez has made available (see the last
link at http://www.eade.com/AccessSIG/downloads.htm). In particular, I direct your attention to
the last paragraph on page 22, which reads as follows:

"The most important point for you to remember is that you will always re-introduce data integrity
problems when you de-Normalize your structures! This means that it becomes incumbent upon you or
the user to deal with this issue. Either way, it imposes an unnecessary burden upon the both of
you. De-Normalization is one issue that you'll have to weigh and decide for yourself whether the
perceived benefits are worth the extra effort it will take to maintain the database properly."


Tom
_________________________________


you can do this with an update query but if it is a
calculated field, it is usually not recommended to add
this to the table. usually(and recommended by MS and MVPs)
the fields that can be calculated are left out of the
tables and when needed for a report or form display, the
control source for the report control or form control is
the formula that begats the product of the calculation.
this is done to same disk space. general rule - never
store in a table data that can be calculated. this is
looked on as redundant data.
good luck.

_____________________________________




I have a table with 5 fields, they are start date (data type = Date/Time);
purchases, years, cost of sales and profit (all have data type = number)
within this table I need to create a calculated field that will do the
following: Calculate the Years of doing business with each customer by
subtracting the Start Date from 8/14/2003. Use 365.25 for the number of days
in a year. Use an Integer to represent the number of complete years. Do not
include partial years and do not round up. Any decimal places should be
truncated.

I can do this creating a query, but can not figure out how to do it within
the table.