How to Create a Calculated Field in a Table with Access 2000

  • Thread starter Thread starter Gregory I. Hayes
  • Start date Start date
G

Gregory I. Hayes

Is it possible to create a calculated field in a MS Access 200 Table? I know
how to create them in forms and queries, but can't find where to indicate
the field is calculated or where to put the calculation in table design
view. Right clicking on the a field in table design views shows a build
symbol but it only lets you select a template field type such as Employee or
Cost. I am temporarly using querries instead of tables for my web, but I
would like to have the calculation in the table if possible.

Thanks for any replies
Gregory I. Hayes
(e-mail address removed)
 
Gregory,

That's what queries are for.

You could have a function run through your table, do the calculation and
update a particular table field, but why go to all that trouble? Just put
the calculation in a query.

Hope this helps,
 
Gentlemen;

I have a similar problem, but one where the data is of an open ended
architecture to allow our users to create their own reports using queries
from other 3rd party reporting tools. At what point is it better to store
calculated data that may be necessary for a report to avoid having to run
multiple queries to get the data?
 
There is no way to put a calculated field in a table itself. Using a
query to create the calculation like you have been doing is the best
way to accomplish this.
 
Is it possible to create a calculated field in a MS Access 200 Table?
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
I am temporarly using querries instead of tables for my web, but I
would like to have the calculation in the table if possible.
Click to expand...

You're doing it correctly.

Newsgroups trimmed therefore posted&emailed; please reply to the
microsoft.public.access.tablesdbdesign newsgroup.
 
At what point is it better to store
calculated data that may be necessary for a report to avoid having to run
multiple queries to get the data?
Click to expand...

Never.

Implication of 2NF is that data that don't depend on the key do not belong
in the table. The reason for this is that it's not possible to guarantee
that every summary field will be updated with every table edit. Okay, you
can munge it with upmarket servers that have triggers, but that's a
software workaround not a formal solution.

If you have a really complex report that takes a long time to run, you have
two possile solutions: get a faster server or risk incorrect results.
Sometimes the latter is more acceptable than the former, but the choice can
only be made in the context of the business case.

HTH


Tim F
 
Back
Top