Calculating Fields in a Table

  • Thread starter Thread starter TheJoneser
  • Start date Start date
T

TheJoneser

I am building a table that will function as an amortization table for a loan.

The fields are:
BegBalance
Payment
Principal
Interest
EndBalance

The "Interest" and "EndBalance" fields could be calculated using
combinations of the other fields. I was wondering if there is a way for the
table to automatically calculate those fields if I complete the others?
 
The "Interest" and "EndBalance" fields could be calculated using
combinations of the other fields. I was wondering if there is a way for the
table to automatically calculate those fields if I complete the others?
Click to expand...

Normally you calculate fields in a query, not a table. In a query,
you can specify new columns and give them a formula (or 'expression')
that will calculate the values for each record. Then you can use the
query whenever you would have used the table.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
There really is no reason to store these calculated values in the table
itself. You would do best to calculate those values using a query.

Should you have any forms that you want to create based on all of this data,
you can simply set the query as the form's recordsource instead of the table.
 
The "Interest" and "EndBalance" fields could be calculated using
combinations of the other fields. I was wondering if there is a way for the
table to automatically calculate those fields if I complete the others?
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.

John W. Vinson [MVP]
 
Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it
Click to expand...

You really propose an amortization calculation in a Query without
storing calculated values in a table? John, please post your SQL!

Jamie.

--
 
You really propose an amortization calculation in a Query without
storing calculated values in a table? John, please post your SQL!
Click to expand...

That's why the good Lord allowed Microsoft to develop VBA. <g>

John W. Vinson [MVP]
 
That's why the good Lord allowed Microsoft to develop VBA. <g>
Click to expand...

I note the grin but in case you are only half joking...

By "VBA" you are presumably alluding to a UDF in the Access layer. In
such a scenario the calculation would be external to the storage. Even
if the storage was a 'native' Jet database, it would still be
external: Jet has 'knowledge' of VBA in the sense that it shares
components -- e.g. Jet 4.0 utilizes the VBA5 Expression Service --
however Jet cannot make calls to your VBA modules.

If there are exceptions to your rule of thumb about storing derived
values, surely a situation where the calculation is complex and
external to the SQL DBMS would be one of them?

Obviously, if the calculation can be expressed in syntax understood by
the storage (e.g. Jet SQL DML) then a CHECK constraint (or similar)
could be defined to ensure the derived values don't go out of synch
(other SQL DMBs have build in functionality to keep values in sync
e.g. SQL Server has triggers and calculated columns). Would you
consider this another an exception to your rule?

Jamie.

--
 
Back
Top