Storing Calculated fields in underlying table. (Access 97)

[davidrsutton]

I'm sure this has been answered elsewhere on here but I can't seem to find it!

I have a form (frmContract) that is based on a query using 2 tables (tblContract and tblPost). On this form are 3 fields ([Hours], [tblPost].[hours] and [JobSection]). the field [Hours] is a calculated control (= [tblPost].[Hours]*[JobSection]).

Now, all I want to do is save the result of this calculation into the [Hours] field of the underlying table tblContract. I have found answers on MS knowledge base using SetValue in a Macro but it doesn't seem to work with Access 97.

Any help or ideas will be greatly appreciated!

Many thanks,

Dave S.

 

[jimirvine]

It is bad practice to save calculated values to a table doing so will very often lead to erroneous data in the database. Take a calculated value of Age for example. The age column would need to be reassessed and updated on a day by day basis otherwise the Age data would be out of date almost constantly. Calculated values should be calculated 'On Demand' via queries, or expressions on reports and forms.

Jim

 

[davidrsutton]

How else can I do this though:

tblpost.hours holds how many hours a particular post has and tblcontract.jobsection is a figure between 0 and 1 that signifies how much of the post a person takes, and tblcontract.hours shows how many hours that contract is covering.

e.g. If a post is for 37 (stored in tblpost.hours) hours a week, we may have 2 people (personA and personB) jobsharing a post. So when I draw up a contract for PersonA I select the post they are working in which automatically displays the value in tblpost.hours (in this example 37). In the next field (tblcontract.jobsection) I enter the share of the job they are doing (e.g. 0.8 if they are doing that job for 4 days a week out of 5). On the Afterupdate of tblcontract.jobsection, the system calculates how many hours that person will be working (tblpost.hours * tblcontract.jobsection) and displys it in the tblcontract.hours field

This field isn't like an age calculation which (obviously!) changes all the time, so I imagine it would be more robust as the calculation only happens when a new contract is being drawn up or an existing one is being changed, which isn't all that often.

Again, any help or advice would be warmly welcomed.

Dave S.

 

[jimirvine]

Hi Dave.
To reiterate, storing calculated values goes against the rules of normalization. There is very rarely a case for storing calculated values, and only ever if that value will never ever ever change. To get the value that you want to work with simply create a calculated field in a query that calculates the value and run the query as and when you require that info. Alternatively create a calculated control on a form or report to do the same thing.
It is of course entirely up to you as to whether you want to take my advice or not (it is after all your database ) but I would strongly advise you to avoid storing calculated values.
HTH
Jim

 

[storyboy]

Trust me, I received the same thrashing from others on this subject, finally after persistant inquire got an answer. There are rules, and there are exceptions. Check my thread 702-1023228. I had had it and was going to trash access and do my project over in another system, then I got an answer so was able to same all my efforts, it was that important to me. Some times it is necessary to do it, break the rules. Stay with it.
Tom

 

[davidrsutton]

Thanks Tom, the original Tek-tips rebel without a cause!!! I did find a way to get round having to do it this way but I will have a look at your thread and see how it goes.

Thanks,

Dave