calculations within a field

  • Thread starter Thread starter T Kalnoske
  • Start date Start date
T

T Kalnoske

I am working with access 2k2 and am trying to incorporate
a spreadsheet in my database that performs calculations on
fields from two tables in the same database. Any
assistance is appreciated in advance. Thanks!

for example:
I have the following fields:
surcharge
withdrawals
I would like to set up a field within the same table or a
different table that would multiply
surcharge*withdrawals*.50

BUT, I need the calculation to be for just that record,
not all records. Again, thanks for any replies.

Tom
 
Well ... you have to forget Excel when you work in Access. Excel is a good
spreadsheet application and Access is a relational database application.
They are used differently.

In Access Table, you *don't* store Calculated Values in Tables as storing
them may lead to lots of problems later. Calculated Values should simply be
re-calculated whenever you need them. For example, you can design a Query
with the required expression in a Column (of the Design Grid) as the
"Calculated Column". When you view the Query in DatasheetView, the
calculated values will be re-calculated and displayed (just like a
spreadsheet ...)
 
this will work if i am doing the same calculation for all
records, but I have 1250 records and several different
calcualtions.
Any ideas?
 
Yes!

In a standard module, create a function named CalculateColumns(InputField as
InputField Type) with a type as required .
The function will produce the correct calculation based on the value of
the input. You can do that with a SELECT statement (preferred) or with a
series of IF statements.

Now have the column CalculatedColumn: call your new function,
CalculateColumns.

CalculatedColum: =CalculateColumns

HTH
 
Back
Top