Auto Calculations

  • Thread starter Thread starter Carol
  • Start date Start date
C

Carol

My database tracks water consumption so service dates can
be forecast. Each location has a certain size water
filter that has a specific useful life with the quality
of water.

The main form has the customer information, locations,
etc. The sub-form is like an Excel spread sheet.

At present I am entering the data in the subform. Date,
Meter Reading, No. of days since last meter reading and
finally average consumption per day. This average
consumption lets us forecast the next service date.

I cannot find any way to make Access automatically make
the calculations and "save them" to the subform.

I was thinking that I might be able to export the subform
to Excel so that the calculations would be made
automatically after manually entering the date and
current meter reading and then re-import to Access. This
would eliminate the many human errors I encounter with my
staff (as well as myself).

Is this a possibility or a pipe dream?
 
There is no reason to store the result of calculations (generally speaking)
in a database. The database can calculate it dynamically anytime you want
it. However, while a table (or datasheet) may look like a spreadsheet, it
is not.

If your subform is in continuous form view, you can have a control in the
footer where the ControlSource is something like this: =avg([MeterReading])
and it will display the average. However, if it is in datasheet view, you
can't do this. Inthis case, you could display the average in a textbox on
the main form.

On my website, (see sig below) there is a small sample database called
"FormSubform.mdb" which illustrates this. (Actually it displays the total,
but average would work the same.)
 
Back
Top