calculations in a table

  • Thread starter Thread starter WStoreyII
  • Start date Start date
W

WStoreyII

is there any way to do calculations in the table its self with out using a
form
like you would in excel say for instance a table with three fields
field 1) rate
field 2) time
and then field three would be d=r*t
like can in excell

thanks for the help
 
Yes, create a calculated field using SQL. Use a calculated label.Caption in
a form... Where do you want this calculated data?
 
Hi,
is there any way to do calculations in the table its self with out using a
form
like you would in excel say for instance a table with three fields
field 1) rate
field 2) time
and then field three would be d=r*t
like can in excell
Click to expand...

Yes, you can create a query with the two fields from the table and the 3'rd
field as calculated field

select rate, time, d as rate*time from <yourtable>

HTH
Bogdan Zamfir
_________________________

Independent consultant
 
is there any way to do calculations in the table its self with out using a
form
like you would in excel say for instance a table with three fields
field 1) rate
field 2) time
and then field three would be d=r*t
like can in excell

thanks for the help
Click to expand...

No, you cannot - and you SHOULD NOT.

Access is *not a version of Excel*. Excel is a spreadsheet; Access is
a relational database. 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 in the control source of a Form or a
Report textbox. Just create a Query based on the table; in a vacant
Field cell you can type

D: [Rate] * [Time]

The query will return the appropriate value for each selected row.
 
how does one create a calculated field i would like to use expressions of
various types in several fields because i like that data table view looks
the best i dont like the forms

at least untill i can understand how to program and then make some but that
is to hard for me right now
 
WStoreyII said:
is there any way to do calculations in the table its self with out using a
form
like you would in excel say for instance a table with three fields
field 1) rate
field 2) time
and then field three would be d=r*t
like can in excell
Click to expand...

The correct answer is no.
You must use a query based on a table to have an Excel like feature and in
general this is the best way to operate.
In some cases you may use a query or function to update a field (the "d"
field in your case) but you can't have equations in a table in a relational
data base. (Some other data bases do allow this.)

When is it proper to update a table?
My best answer is to get a firm grip on why you should *hardly ever* do
this, then you will know when it's OK to do it.
 
If you don't understand, *re-read and follow* John
Vinson's advice.

Calculated values should be re-calculated in Queries and
should not be stored in your Tables as specified by the
Relational Database Design Principles. The DatasheetView
of the Query will display the stored data + calculated
values just like the DatasheetView of the Table.

Perhaps, you should read up on a book on designing
relational databases.

HTH
Van T. Dinh
MVP (Access)
 
What you need to do is create a querry. Use the querry wizard. Copy all
fields from your database. Once it is made right click on the top of the
pane and select Design View. In the first empty column, first line type this
with no quotes "d:([r]*[t])". After you have done this right click on the
top of the pane and select Datasheet View. I hope this helps.
Jay
 
how does one create a calculated field i would like to use expressions of
various types in several fields because i like that data table view looks
the best i dont like the forms
Click to expand...

That's a little like saying "I like Excel but I don't like to use
expressions in cells". Forms are an essential tool for any productive
use of Access.
at least untill i can understand how to program and then make some but that
is to hard for me right now
Click to expand...

As noted earlier, you CANNOT do this in a Table and should not try.
(Sorry, but that's just a fact: James and Bogdan both said "use a
query" and that's what you need to do).

If you like the datasheet view, create a Query and open it as a
datasheet (and put up with the fact that datasheets are very limited
in their capabilities).
 
Back
Top