- Local time
- Today, 18:58
- Joined
- Jul 9, 2003
- Messages
- 17,511
Should I use them or not?
I would be interested in arguments for and against. Cheers Tony. .
I would be interested in arguments for and against. Cheers Tony. .
Calculated Fields in Tables
- Thread starter Uncle Gizmo
- Start date
Minty
AWF VIP
- Local time
- Today, 18:58
- Joined
- Jul 26, 2013
- Messages
- 10,670
I've used them a few times in SQL tables, and they can be handy.
If you use the results frequently in the front end, it can save quite a lot of work.
I can't really see any obvious argument against them, after all if they are a calculation that you would have to do later it's probably going to be more efficient.
If you use the results frequently in the front end, it can save quite a lot of work.
I can't really see any obvious argument against them, after all if they are a calculation that you would have to do later it's probably going to be more efficient.
Anakardian
Registered User.
- Local time
- Today, 19:58
- Joined
- Mar 14, 2010
- Messages
- 173
I think it really depends on how it is calculated.
If one of the inputs that are used is liable to change it might be better to do the pre-calculated field in a different manner. Otherwise you might end up with a cascade of calculations running in circles.
If the result is not going to change it may or may not be a good solution. It really depends on how the calculations behave when updates happen.
I think I would calculate the result with a form or code and then save the result if that is what I want to do. That way I know exactly what is happening and how to do any corrections consistently.
If one of the inputs that are used is liable to change it might be better to do the pre-calculated field in a different manner. Otherwise you might end up with a cascade of calculations running in circles.
If the result is not going to change it may or may not be a good solution. It really depends on how the calculations behave when updates happen.
I think I would calculate the result with a form or code and then save the result if that is what I want to do. That way I know exactly what is happening and how to do any corrections consistently.
- Local time
- Today, 18:58
- Joined
- Jul 9, 2003
- Messages
- 17,511
So that would mean if one of the values in a Field the calculation was based on was changed, then the calculated value would change. That's a very good thing.
- Local time
- Today, 12:58
- Joined
- Feb 28, 2001
- Messages
- 30,501
There are times when a calculated field makes sense from a practical viewpoint if the calculation is one that could be done one time and the result stored for later use.
One of the other threads had a case that I consider a reasonable example. The OP of that thread wanted to keep activity logs based only on knowing transition events (turned ON, turned OFF, etc.) where he had a state and a timestamp as individual records from the device in question. My thought was that to compute run-time for service scheduling, he might add a field to the record showing how long it was in that state by consulting the time on the next record. Here, the computation is not intra-record but inter-record.
The total spent in a particular state becomes a trivial "SUM query with GROUP BY" exercise if you store the time in-state in the event record corresponding with entering that particular state.
Now, if all the things you need are intra-record, I would think that a calculated field would be superfluous and a query would be exactly right for the job. But because of the issue of computing time-in-state for the OP's described data set, a one-time computation stored in that table would be appropriate. Granted, it would have been better had the OP stored BOTH times in the same record, but that wasn't what his hardware monitoring device gave him.
One of the other threads had a case that I consider a reasonable example. The OP of that thread wanted to keep activity logs based only on knowing transition events (turned ON, turned OFF, etc.) where he had a state and a timestamp as individual records from the device in question. My thought was that to compute run-time for service scheduling, he might add a field to the record showing how long it was in that state by consulting the time on the next record. Here, the computation is not intra-record but inter-record.
The total spent in a particular state becomes a trivial "SUM query with GROUP BY" exercise if you store the time in-state in the event record corresponding with entering that particular state.
Now, if all the things you need are intra-record, I would think that a calculated field would be superfluous and a query would be exactly right for the job. But because of the issue of computing time-in-state for the OP's described data set, a one-time computation stored in that table would be appropriate. Granted, it would have been better had the OP stored BOTH times in the same record, but that wasn't what his hardware monitoring device gave him.
