Storing Calculated Fields (TextBox)

[Michael Pfennings]

G'day guys, I'm going to try and describe my problem as best as I can. I've been programming Database's for a little while now, and I used to program in RBase and Q&A years ago, well I now have been given the task of redesigning our Service Database. Ok my question is how do I store a calculated field, example. I have a calculation that is a formula stored either in a Query or in the Control Source of a textbox, my problem is I want these results to be stored in the table, not as a calculated answer, I need to send this info to our accounting software. I remember doing it VB, but its been a while since I programmed in VB. Basically I know I need to copy the contents of a field to another in VB.... I think. I'm hoping someone could just pluk the answer out of the sky. But until then I'm hitting the books. I would appreciate any help, or at least something to jar my memory. PS.. I just want to note that Access 2003, is a real achivement and look forward to using the new features.. one step at a time. Thanks Guys...

 

[Rick Brandt]

G'day guys, I'm going to try and describe my problem as best as I can. I've

been programming Database's for a little while now, and I used to program in
RBase and Q&A years ago, well I now have been given the task of redesigning our
Service Database. Ok my question is how do I store a calculated field, example.
I have a calculation that is a formula stored either in a Query or in the
Control Source of a textbox, my problem is I want these results to be stored in
the table, not as a calculated answer, I need to send this info to our
accounting software. I remember doing it VB, but its been a while since I
programmed in VB. Basically I know I need to copy the contents of a field to
another in VB.... I think. I'm hoping someone could just pluk the answer out
of the sky. But until then I'm hitting the books. I would appreciate any help,
or at least something to jar my memory. PS.. I just want to note that Access
2003, is a real achivement and look forward to using the new features.. one step
at a time. Thanks Guys...

First you should explain why you want to do this. Storing calculated values in
tables is almost always a bad idea. There are a few exceptions and perhaps your
situation is one of them, but you already have indicated that you can construct
a query that does this calculation for you so why not use that? You say you
need to "send the data to our accounting software". It should be just as easy
to do this with the query as it is with the table.

 

[Michael Pfennings]

Ok, here's the deal. A part of the Service Database the engineer is required to enter some info, like Travel, Service Provider, What Rate. etc... Some of it is entered on the form, the rest is calculated based on what options the Engineer picks.. This info is then calculated in a Text Box (Control Source)(Eg. "=IIf([ActivityOSorWS]="WorkShop",IIf([PerHourRate]............"). Now thats all great for a result.. but if I was to invoice this client the amount would be correct and current to today's rates. But because my Rate's are listed in a table for future additions and changes, this value becomes a variable. So for example if I was to change the rate, and say the client has lost his invoice and we just had an adjustment to our rates. Well with the currrent setup and I changed the rates, the recalculation of that invoice will refelect the changes, and not the original rate. If I was able to copy the results and record them in a field. I could use a switch that will lock that record from any changes once that job is closed. Does this make any sense.. I know there's a better way, but I'm still seeking a better way. The way things were done before was that the Engineer put in the figures themselves.. however this has proven in incorrect amounts and losses in revenue.. I'm trying automate this all. I hope this helps.. Thanks again

 

[Rick Brandt]

Ok, here's the deal. A part of the Service Database the engineer is

required to enter some info, like Travel, Service Provider, What Rate.
etc... Some of it is entered on the form, the rest is calculated based on
what options the Engineer picks.. This info is then calculated in a Text
Box (Control Source)(Eg.
"=IIf([ActivityOSorWS]="WorkShop",IIf([PerHourRate]............"). Now
thats all great for a result.. but if I was to invoice this client the
amount would be correct and current to today's rates. But because my
Rate's are listed in a table for future additions and changes, this value
becomes a variable. So for example if I was to change the rate, and say
the client has lost his invoice and we just had an adjustment to our rates.
Well with the currrent setup and I changed the rates, the recalculation of
that invoice will refelect the changes, and not the original rate. If I
was able to copy the results and record them in a field. I could use a
switch that will lock that record from any changes once that job is closed.
Does this make any sense.. I know there's a better way, but I'm still
seeking a better way. The way things were done before was that the
Engineer put in the figures themselves.. however this has proven in
incorrect amounts and losses in revenue.. I'm trying automate this all.
I hope this helps.. Thanks again

Two standard ways to handle this.

(1)
Store all values necessary to derive the final calculation. In your case
whatever rate is variable would be retrieved from the lookup table one time
and stored as part of the record. An equivalent example is on an Invoice
or Order. You store the ItemID, the Qty, and the current Unit-Price. This
allows you to calculate the extended price at any time and since you stored
the Unit-Price with the record it is not affected by future changes in
Unit-Price.

(2)
You modify your rate lookup table to include a history of all changes with
a StartDate and EndDate. Then your lookup for individual records can use
the date of the record to retrieve the appropriate rate based on the
StartDate and EndDates. I find this method a lot more complex, but a lot
of people use it.

 

[Tim Ferguson]

Well with the currrent setup and I changed the rates, the
recalculation of that invoice will refelect the changes, and not the
original rate. If I was able to copy the results and record them in a
field. I could use a switch that will lock that record from any
changes once that job is closed. Does this make any sense..

Yes it does make sense. The problem is that the phrase "calculated field"
round here nearly always makes people jump into "redundant-data-violates-
2NF" mode, but this is one of those not-quite-rare situations where the
result of the calculation is genuinely still dependent on the Key and
therefore appropriate for 2nd NF.

The answer to the original question, then, is to use the BeforeUpdate event
of the data entry form, with something like

txtInvoiceTotal = CCurr(WorkHours * DLookUp("RatePerHour","Rates", _
"WorkTypeCode = """ & txtWorkType & """") + _
CCurr(txtTotalGoodsValue) + _
Overhead(txtWorkType)

that is to say, reading the values from other fields, global const values,
userdefined functions and whatnot. Note the use of DLookUp to get the
currently active rate. If you want the user to have a better view of the
result of all this, you could put on a command button called "Calculate"
and use its Click event to call the same code.

Hope that helps


Tim F