storing calulations

  • Thread starter Thread starter Steve Fama
  • Start date Start date
S

Steve Fama

How do I store calulations in a table. I have calc. in a
form, how do I store the results in a table. Please help
 
Steve Fama said:
How do I store calulations in a table. I have calc. in a
form, how do I store the results in a table. Please help
Click to expand...

In almost all cases you DON'T. If they can be calculated they don't need to be
stored and it is bad design to do so. Can you explain why you want to?
 
Rick, on a tangent - almost!,
I can sympathise on why Steve wants to do something like this, and I am
interested to see if it can be done.

Each month I have to tally up the mileage and fuel purchased for a
substantial fleet of vehicles, and have to do it from hand-written sheets.
At the moment I use Access to calculate the start and end mileage difference
(naturally), and then have to use Calc.exe to total the number of litres
used for those miles/kilometres (which can be many entries). This means
'alt-tabbing' between Calc and Access. If the ability to use the Calc.exe
was available, or something similar on the same screen, it would certainly
make my life a lot easier!

Perhaps you can suggest some code to add up the multiple fuel purchases and
place it in the 'fuel_used' field?

TIA

DubboPete
 
Rick, on a tangent - almost!,
I can sympathise on why Steve wants to do something like this, and I am
interested to see if it can be done.

Each month I have to tally up the mileage and fuel purchased for a
substantial fleet of vehicles, and have to do it from hand-written sheets.
At the moment I use Access to calculate the start and end mileage difference
(naturally), and then have to use Calc.exe to total the number of litres
used for those miles/kilometres (which can be many entries). This means
'alt-tabbing' between Calc and Access. If the ability to use the Calc.exe
was available, or something similar on the same screen, it would certainly
make my life a lot easier!

Perhaps you can suggest some code to add up the multiple fuel purchases and
place it in the 'fuel_used' field?

TIA

DubboPete
Click to expand...

The reason for not storing calculated data is quite straight forward.
Data is sometimes incorrectly entered.
Correcting the incorrect data entry will leave the erroneous
previously calculated result.
If you can compute the calculation from already stored data, there is
no need to store the result. Re-compute it whenever needed.
Storing the result of a calculation wastes memory and storage space.

There are exceptions, of course, such as storing invoice data where
the cost of items on a particular date may have since been changed.

Here is one method to compute mileage driven using a query.

SELECT tblMultiCars.Auto, tblMultiCars.dteDate, tblMultiCars.Odometer,
Abs([Odometer]-DLookUp("Nz([Odometer])","tblMultiCars","[Auto] = '" &
[Auto] & "' and Month([dteDate]) = " & Month([dteDate])+1)) AS
MilesTraveled
FROM tblMultiCars
ORDER BY tblMultiCars.Auto, tblMultiCars.dtedate;


The Auto field holds the vehicle name, dteDate is the date driven, and
Odometer is the odometer reading on the dteDate.

Auto dtedate Odometer MilesTraveled
Caddy 01/01/2002 5000 1000
Caddy 02/01/2002 6000 500
Caddy 03/01/2002 6500
Ford 01/01/2002 3000 750
Ford 02/01/2002 3750 250
Ford 03/01/2002 4000
VW 01/01/2002 1000 450
VW 02/01/2002 1450 550
VW 03/01/2002 2000

You would use vehicle ID numbers, to differentiate the vehicles, but
this would give you a start.
 
As everyone has pointed out, you generally do not want to do this, and
if your design produces up with a requirement to store a calculated
result, you should review it very critically, since there is almost
always a better (more relationally sound) way of doing what you are
trying to do. However, if you are sure you want to do this, the most
straightforward way to do it is to put code into the AfterUpdate Event
of all the user-modifiable fields which contribute to the calculation
which includes a specific assignment of the calculated result to a
field in the table. A very simple-minded example:

Your form, bound to your table, has two text boxes on it (which may be
bound to fields in the table, but don't have to be) called txtA and
txtB, and you want to save the sum of the values in those two text
boxes into a field in your table called CalcValue. In the AfterUpdate
Event code for _each_ of the textboxes, you will put the line

CalcValue= txtA + txtB

It's a lot easier to do than to describe.

How do I store calulations in a table. I have calc. in a
form, how do I store the results in a table. Please help
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top