Calculated Fields (1 Viewer)

Calaculated fields should never be stored in a database. Is this always the case -

For example if I am asked how old I am - I simple answer with the age I have stored in my mind, I don't calculate the differance in years between today and the day I was born. ( i know as I get older - I do have to do this on occasions ) Simply storing my age is easier than the calculation everytime. (i am not suggesting storing ages against in an actual database) - is it not a good idea to store a calculated value if the calcualtion is harder to perform.

Or I currently have 100 people in a study with an average IQ of 100 - I add one more person to the study who has an IQ of 140. I don't need to do the calculation again of adding all 101 persons IQs and dividing by 101. Just to times the average by the 100 add 140 and divide by 101.

(again in this example I am not suggesting this is a good idea to store in an actual database) - but at a certain point (many times more powerful a computer may be), is a calculated field more efficient than calculating the value, even in a database?

The problem with storing calculated values is not one of efficiency. It is one of validity. I don't believe that anyone here has ever said that it is more efficient to re-calculate. Common sense would tell you otherwise. What was said was that re-calculating leads to more accurate data. If you don't care that your data is old and possibly incorrect, then store the calculated value. Age is a classic example. My age today is x, tomorrow it may be y. If you are looking for 21 year old, blue-eyed, blonds, you might not find me. Storing the order total in the order record is something new comers always want to do. If you do that, you need to place code in a varity of places to make sure that it is always updated. For example, a new item may be added, a quantity might be changed, an item might be deleted, the price might be decreased. Of course, once an order is shipped, these changes won't happen. But, then you need to worry about returns.

You don't need to believe me. Many people far smarter than I, say storing calculated data leads to data anomolies. I simply concur. I have spent over thirty years in the field of application development and I have in fact, caved a couple of times and given in to the impulse to store a calculated value. I have ALWAYS regretted it and almost always modified the application at a later date.

Calculated values are sometimes stored in inventory applications. In a large company, there could be hundreds of thousands of inventory transactions every year. In this case, cycle counts (partial physical inventory) are done once or twice a year. The detail transactions are archived and a present count is added to the active transacton table in their place.

Calculated values are sometimes stored in general ledger applications for the same reason as in the inventory applications. In this case, instead of a cycle count, they close an accounting period.

In both cases, updates to the closed data are NEVER allowed. Adjustments, if necessary, are made to the current inventory or period.

The only application where it is common and correct to store calculated values is a data warehouse. The data warehouse is used for reporting only. No updates are allowed to the tables. The data in the warehouse is refreshed at intervals as frequently as monthly. Sometimes the procedure is to completely replace the warehouse data. Sometimes only new data is appended. In most cases, the warehouse is "down" for a couple of days while it is being "refreshed".

Thanks for the answers, especially Pat - my problem is that a customer wants to see management information - on the start up of a large application - things like top ten cutomers over different services. Anyway - the time taken to do the calculation is prohibitory - certainly to put on everyones startup screen.

I am wondering what else I can do rather than use calculated fields - this kind of info is available in the database via reports and forms, but they want it on startup -admittedly the calculations can probably be done more efficiently, but I doubt they could be done in a time delay most users would be happy with.

Others must have had this problem. What was the solution?

I am basically doing a top ten customers - by differant criteria.

1)Currently I am first querying on a date range (that the user can enter) to give sales (for the period)

2)then from these results I pick out the top ten customers.

3)then from these results - I add in all the customer details I want from the customer table


(Do I need to do 3 queries - is this what is slowing me down)

I need to do this over ranges, ie for product type, department or such (has not yet been decided which)

So end result will be 5 - 8 top ten lists.

(other thing also may be wanted which will take further time to display)

Firstly am I doing the above in the correct way, if yes and my results are not quick enough - is Brians solution the way to go?

Ta - paul

Pauldohert said:

I am basically doing a top ten customers - by different criteria.

1)Currently I am first querying on a date range (that the user can enter) to give sales (for the period)
2)then from these results I pick out the top ten customers.
3)then from these results - I add in all the customer details I want from the customer table

(Do I need to do 3 queries - is this what is slowing me down)

Click to expand...

1 and 2 can be done in 1 query:

i.e.

SELECT TOP 10.......WHERE DateRange Between bla bla;

As for 3, what do you mean by "add in all the customer details" - are you joining the customers table to the query?

1) Qbookings

SELECT Booking.OrganisationID, Booking.TotalFee
FROM Booking
WHERE (((Booking.BookingDate) Between #1/1/2000# And Now()) AND ((Booking.CancellationDate) Is Null));


I haven't grouped here because I can't get it to group when I add other fields? - How do I do this.


2)

SELECT TOP 10 QBookings.OrganisationID, Sum(QBookings.TotalFee) AS SumOfTotalFee
FROM QBookings
GROUP BY QBookings.OrganisationID
ORDER BY Sum(QBookings.TotalFee) DESC;


Then I bring in the Org table to give me the output I want.

I can't seem to do 1 and 2 together or I lose my grouping on OrgID?? Again this is why I then bring in the Org table seperately.

Any help would be appreciated.

Am I making myself clear - probably not!

Got the SQL correct - now to see if its gonna work as I want it to.

I can't seem to do 1 and 2 together or I lose my grouping on OrgID?? Again this is why I then bring in the Org table seperately.

Click to expand...

- this is a bug that I have found with the query builder. If you create a totals query that groups, it converts all criteria to a Having clause. It won't let you use a Where clause. FYI, a where clause is applied PRIOR to grouping and a having clause is applied AFTER grouping. They are not interchangable except when the criteria is applied to a field you are grouping on. Using either or both in a totals query is valid. In fact, if you switch to SQL view, you can modify the query to work as you want. However, if you ever open the query with the builder, Access will "fix" the query for you and it will no longer work properly. So, the safest thing is to use two queries. One to apply the where criteria and then the next one uses the first query and does the aggregation.

Thanks I have it.