A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

storing calulations



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2004, 12:47 PM
Steve Fama
external usenet poster
 
Posts: n/a
Default storing calulations

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
  #2  
Old May 28th, 2004, 01:17 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default storing calulations

"Steve Fama" wrote in message
...
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


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?


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


  #3  
Old May 28th, 2004, 01:34 PM
DubboPete
external usenet poster
 
Posts: n/a
Default storing calulations

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 Brandt" wrote in message
...
"Steve Fama" wrote in message
...
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


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?


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com




  #4  
Old May 28th, 2004, 04:04 PM
fredg
external usenet poster
 
Posts: n/a
Default storing calulations

On Fri, 28 May 2004 22:34:49 +1000, DubboPete wrote:

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 Brandt" wrote in message
...
"Steve Fama" wrote in message
...
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


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?


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



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.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
  #5  
Old May 29th, 2004, 02:58 PM
Peter R. Fletcher
external usenet poster
 
Posts: n/a
Default storing calulations

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.

On Fri, 28 May 2004 04:47:39 -0700, "Steve Fama"
wrote:

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


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


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.