How to use formulas in Access

S

Sharp

Hi

In Excel you can use formulas to generate data for a field.
Is it possible to simulate this in Access?

For example, consider the following table.

tbl_Foo
Field_One
12
234
23
65456
546
5

I want to add a new Field (Field_two) using SQL that will use the formula:
Field_Two = Field_one - 1

Expect this query output:
Field_One Field_Two
12 11
234 233
23 22
65456 65455
546 545
5 4

Any help appreciated

Cheers
Michael
 
B

Brian

Sharp said:
Hi

In Excel you can use formulas to generate data for a field.
Is it possible to simulate this in Access?

For example, consider the following table.

tbl_Foo
Field_One
12
234
23
65456
546
5

I want to add a new Field (Field_two) using SQL that will use the formula:
Field_Two = Field_one - 1

Expect this query output:
Field_One Field_Two
12 11
234 233
23 22
65456 65455
546 545
5 4

Any help appreciated

Cheers
Michael
Click to expand...

SELECT Field_one - 1 AS Field_two FROM tbl_Foo
 
L

Larry Daugherty

Don't store calculated data in your database, recalculate it each time you
need to show it.

HTH
 
J

John Vinson

Hi

In Excel you can use formulas to generate data for a field.
Is it possible to simulate this in Access?
Click to expand...

Access is a relational database. Excel is a spreadsheet. THEY ARE
DIFFERENT!

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. Your tables
should store only the actual data upon which the calculations
are based; and you should use Forms for data viewing, reserving
table datasheets for design and debugging.

John W. Vinson[MVP]
 
Top