arithmetic at table level..

[antonyx]

lets say i have three fields in my table..

all currency fields..

jobprice..
jobcarpark..
jobtotalfare..

is there a way at table level.. to set jobtotalfare as jobprice + jobcarpark because at the moment i am simply ensuring that in all the forms.. if the price or carpark is updated.. so is the total fare..

it would be much easier to do this at table level..

 

[boblarson]

Actually, you shouldn't be storing calculations as you can get, via a query, the total of the two fields by adding them together at runtime.

 

[antonyx]

so are you saying there is not even a need to store the total as a field because at any point in the system.. i can retreive the total by adding whichever fields i wish together?

 

[boblarson]

In the normal course of things, Yes I am saying that. Now, that being said, there may be a few exceptions to that rule (normalization rule). Let's say that you have an item that is priced at a certain price and you invoice for that item at that price. You may need to store the price information with the order if the price varies over time. But, since you are already storing the individual items (jobprice and jobcarpark) you can always get the value of the two added together so you don't need to store the total.

 

[neileg]

Not only is there no need to store it, it's a bad idea. Stored calculations are immediately out of date when you change any of the data that is included. Yes, you can make sure the form updates the stored field, but there are other ways that the data could be changed.

 

[antonyx]

yes.. i understand.

 

[Anthony George]

Why not try a query like this?

Hi

Try a query like this:-

This would result in this when run

Make sure you use a colon : after the fieldname you want to appear at the top of the column and use square brackets.

Regards

Tony

 

[The_Doc_Man]

antonyx, the key to understanding why we are telling you this is a simple one. Both tables and queries are merely RECORDSOURCE material. Forms and reports (and even other queries) just want RECORDSOURCE inputs. They don't usually care from where they came. So if you have computations you need/want to see, just include the computations in a QUERY rather than bust your buns to do it in a table.

One more reason to do this: If the table is big enough, using the query to compute the value on the fly saves a lot of space. Every computation of this class saves you at least four bytes per computed field. Maybe eight if you used DOUBLE number formats. How much is that? You save 1 Kb for every 125 records as SINGLE or 2 Kb for every 125 records as DOUBLE numbers.

Add to that this simple fact. Over the years, as computer CPUs have leaped in speeds by orders of magnitude, disks have maybe doubled or tripled in speed. So anything that makes a record smaller (by omitting something that can easily be computed) means you fit more records into the disk, which means you save space AND TIME in reading the data. Consider that on an Intel box running 1 GHz, an average of 2.5 cycles is required to complete the average instruction. That is 400 MILLION instructions per second. Or 400 THOUSAND instructions per millisecond. A disk read takes 8-10 milliseconds. That 10 milliseconds is 4 MILLION instructions. So compute away, your CPU can handle a lot better than your disk can store it.