how do i insert a formula in microsoft access?

[Guest]

i have created a new project in table and am on design view and was wondering
on how to insert a formula.

please reply a.s.a.p

thanks.

 

[Allen Browne]

In Access, you put the formula into the field in a query, not in the table.

Create the table without this field.
Then create a query, and type the calculation into the Field row.

More info and example:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Douglas J. Steele]

Just to amplify on Allen's advise.

You MUST put the formula into the field in a query, not in the table. Tables
cannot accept formulae.

 

[Tom Ellison]

The statement "Tables cannot accept formulae" is true enough for Jet, but
not for MSDE/SQL Server (which is distributed with every version of Access
since 2000). This can be a valuable tool. It's a calculated column that
updates itself automatically as the values on which it depends are updated.
It's greatest value may be in the ability to index on the result. Other
than that, you could pretty much just put the formula into any query.
However, if that formula may be subject to later change, then the modular
nature of putting that formula into one place where it can be modified later
to change the whole application becomes very important indeed.

Tom Ellison

 

[Douglas J. Steele]

It's seldom recommended that you store computed values in tables. Not only
is it officially a violation of relational database normalization rules, but
as fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

Put the formulae in a query, and use the query wherever you would otherwise
have used the table.