Storing Calculated Values in a Table -- BAD IDEA?

  • Thread starter Thread starter hawk_2001 via AccessMonster.com
  • Start date Start date
H

hawk_2001 via AccessMonster.com

I have read multiple threads regarding storing calculated values in tables.
I do acknowledge that it is generally a BAD IDEA to try to store calculated
values in tables. I have also seen that sometimes it can be okay to do this
in certain circumstances.

I am soliciting advice for a project I am working on.

I have built a "Margin/Revenue Calculator" that allows a user to input
various costs and determine what revenue is necessary to acheive a certain
margin, or see what margin a certain revenue will generate. Before going
further, I must state that none of these calculations are being stored in a
table. I am only storing the parameters for the calculations (miles traveled,
hourly wage, etc.), and letting Access compute the calculations at run-time.

I am having trouble with a few of my variables. One is an "Employee Benefits
Cost". What state an employee works in, and his current salary rate dictates
what his "Benefits to Salary Ratio" is. For example, a driver in Georgia
making $25,000/year has a benefits cost ratio of roughly 45% (it costs the
company $25,000 + 45% ($11,250) per year to employ this person).

I have a query that calculates this percentage, and I want to be able to
store this percentage in a table (because the values for each calculation
will change). I am needing a "snapshot" of the record and its "declared"
parameters in case an executive needs to view it in the future. I do not
want the user to have to go through the motions of setting a benefits cost
each time they look at a record.

Right now, I can get a text box to reflect this calculation, using the
DLookUp function. But, since I can't bind the text box to a table AND
display the value on a form as an expression, I am stuck.

Thoughts? Any help is greatly appreciated.

Thanks in advance.
 
Forgot to add the following:

My table is called "Margin/Revenue Calculator" and the field I am trying to
store values in is called "Benefits Cost". It is a "Number" field, Long
Integer, etc.

I have a Form called "Margin/Revenue Calculator", with a command button to
launch another form called "Employee Benefits Cost Calculator." On the
Employee Benefits Cost Calculator, the user will select criteria from 4
different combo boxes (Region, State, Employee Type, and Salary). Based on
these criteria, the Benefits to Salarty Ratio will be calculated (in a qry
called "Ratio Calculations"). i have a command button on the "Employee
Benefits Cost Calculator" that will requery the "Benefits Cost" text box in
order to display the ratio on the "Margin/Revenue Calculator" form.

In the ControlSource of [Benefits Cost] (on my Margin/Revenue Calculator Form)
I have the following expression:

=IIf(Forms![Employee Benefits Cost Calculator]!BenToSalRatio>0,DLookUp("[Ben
to Sal Ratio]","Ratio Calculations","[Ratio Calculations]![Salary] =Forms!
[Employee Benefits Cost Calculator].[cboSalary]"),0)

This will return the correct value. But, I cannot get the value to write to
the current record.

Again, thanks in advance for any help.
 
hawk_2001 via AccessMonster.com said:
Forgot to add the following:

My table is called "Margin/Revenue Calculator" and the field I am trying to
store values in is called "Benefits Cost". It is a "Number" field, Long
Integer, etc.

I have a Form called "Margin/Revenue Calculator", with a command button to
launch another form called "Employee Benefits Cost Calculator." On the
Employee Benefits Cost Calculator, the user will select criteria from 4
different combo boxes (Region, State, Employee Type, and Salary). Based on
these criteria, the Benefits to Salarty Ratio will be calculated (in a qry
called "Ratio Calculations"). i have a command button on the "Employee
Benefits Cost Calculator" that will requery the "Benefits Cost" text box in
order to display the ratio on the "Margin/Revenue Calculator" form.

In the ControlSource of [Benefits Cost] (on my Margin/Revenue Calculator Form)
I have the following expression:

=IIf(Forms![Employee Benefits Cost Calculator]!BenToSalRatio>0,DLookUp("[Ben
to Sal Ratio]","Ratio Calculations","[Ratio Calculations]![Salary] =Forms!
[Employee Benefits Cost Calculator].[cboSalary]"),0)

This will return the correct value. But, I cannot get the value to write to
the current record.


hawk_2001 said:
I have read multiple threads regarding storing calculated values in tables.
I do acknowledge that it is generally a BAD IDEA to try to store calculated
values in tables. I have also seen that sometimes it can be okay to do this
in certain circumstances.

I am soliciting advice for a project I am working on.

I have built a "Margin/Revenue Calculator" that allows a user to input
various costs and determine what revenue is necessary to acheive a certain
margin, or see what margin a certain revenue will generate. Before going
further, I must state that none of these calculations are being stored in a
table. I am only storing the parameters for the calculations (miles traveled,
hourly wage, etc.), and letting Access compute the calculations at run-time.

I am having trouble with a few of my variables. One is an "Employee Benefits
Cost". What state an employee works in, and his current salary rate dictates
what his "Benefits to Salary Ratio" is. For example, a driver in Georgia
making $25,000/year has a benefits cost ratio of roughly 45% (it costs the
company $25,000 + 45% ($11,250) per year to employ this person).

I have a query that calculates this percentage, and I want to be able to
store this percentage in a table (because the values for each calculation
will change). I am needing a "snapshot" of the record and its "declared"
parameters in case an executive needs to view it in the future. I do not
want the user to have to go through the motions of setting a benefits cost
each time they look at a record.

Right now, I can get a text box to reflect this calculation, using the
DLookUp function. But, since I can't bind the text box to a table AND
display the value on a form as an expression, I am stuck.
Click to expand...
Click to expand...


You need to use VBA code to calculate the value and put it
in a text box bound to the field:

Me.[Benefits Cost] = IIf(Forms![Employee Benefits Cost
Calculator]!BenToSalRatio>0,DLookUp("[Ben to Sal Ratio]",
"Ratio Calculations","[Ratio Calculations]![Salary] =Forms!
[Employee Benefits Cost Calculator].[cboSalary]"),0)
 
Hi -

IMHO, there are times when storing calculated data can be used, and it looks
like this may be one. Since your data represents "snapshots" in time, but
the calculated values may depend on external variables which change with time
(e.g. tax rates), then recalculating each time you use the data would in fact
lead to wrong results. So in this case, you should keep the calculated
results in a table. The usual caveat to doing so still applies, of course -
you have to be absolutely sure that once a record is saved, the data (in the
record) upon which the calculations are based cannot change.

As an aside, I have an application where virtually all the data used for
analysis is calculated and stored in tables. I do this for two reasons:
first, the data is summary-type, and I know the source data for the
calculated data (it's in other tables) cannot change; second - the
calculations are CPU intensive (lots of arrays and such), and take a few
seconds each time. It's much more efficient to calculate only once, and keep
the results.

So, yes there are times when keeping calculated data is fine, and even
neccessary.

John


hawk_2001 said:
Forgot to add the following:

My table is called "Margin/Revenue Calculator" and the field I am trying to
store values in is called "Benefits Cost". It is a "Number" field, Long
Integer, etc.

I have a Form called "Margin/Revenue Calculator", with a command button to
launch another form called "Employee Benefits Cost Calculator." On the
Employee Benefits Cost Calculator, the user will select criteria from 4
different combo boxes (Region, State, Employee Type, and Salary). Based on
these criteria, the Benefits to Salarty Ratio will be calculated (in a qry
called "Ratio Calculations"). i have a command button on the "Employee
Benefits Cost Calculator" that will requery the "Benefits Cost" text box in
order to display the ratio on the "Margin/Revenue Calculator" form.

In the ControlSource of [Benefits Cost] (on my Margin/Revenue Calculator Form)
I have the following expression:

=IIf(Forms![Employee Benefits Cost Calculator]!BenToSalRatio>0,DLookUp("[Ben
to Sal Ratio]","Ratio Calculations","[Ratio Calculations]![Salary] =Forms!
[Employee Benefits Cost Calculator].[cboSalary]"),0)

This will return the correct value. But, I cannot get the value to write to
the current record.

Again, thanks in advance for any help.
I have read multiple threads regarding storing calculated values in tables.
I do acknowledge that it is generally a BAD IDEA to try to store calculated
Click to expand...
[quoted text clipped - 30 lines]
Thanks in advance.
Click to expand...
Click to expand...
 
Thanks for the quick responses.

However, I still cannot get the desired figures. I used the suggested VBA
code and the value is still not being stored in the field.

I tried using the VBA code in the "After Update" property of the text box and
of the form, but neither seemed to work.

Any other suggestions? Am I putting the VBA in the wrong place?

Thanks again.
You need to use VBA code to calculate the value and put it
in a text box bound to the field:

Me.[Benefits Cost] = IIf(Forms![Employee Benefits Cost
Calculator]!BenToSalRatio>0,DLookUp("[Ben to Sal Ratio]",
"Ratio Calculations","[Ratio Calculations]![Salary] =Forms!
[Employee Benefits Cost Calculator].[cboSalary]"),0)
Click to expand...
 
An event **property** should contain:
[Event Procedure]
and the line of code then goes in the corresponding event
**procedure** (reached via the property's builder button
[...] on the right).

Which event's procedure you need to use is determined by
what you do to obtain the parameters necessary for the
calculation. If they are user entered, then you need to use
each combo box's AfterUpdate event to check if all of them
have been entered and, if they have, then do the
calculation. I think(?) maybe each combo boxes' code might
be something like:

If Not (IsNull(Me.combo1) Or IsNull(Me.combo2) _
Or IsNull(Me.combo3) Or IsNull(Me.combo3)) Then
Me.[Benefits Cost] = IIf(Form . . .
End If

using whatever the real names of the combo boxes are.
--
Marsh
MVP [MS Access]


hawk_2001 via AccessMonster.com said:
However, I still cannot get the desired figures. I used the suggested VBA
code and the value is still not being stored in the field.

I tried using the VBA code in the "After Update" property of the text box and
of the form, but neither seemed to work.

Any other suggestions? Am I putting the VBA in the wrong place?
You need to use VBA code to calculate the value and put it
in a text box bound to the field:

Me.[Benefits Cost] = IIf(Forms![Employee Benefits Cost
Calculator]!BenToSalRatio>0,DLookUp("[Ben to Sal Ratio]",
"Ratio Calculations","[Ratio Calculations]![Salary] =Forms!
[Employee Benefits Cost Calculator].[cboSalary]"),0)
Click to expand...
Click to expand...
 
Gotcha, however I do have one more concern.

My [Benefits Cost] text box is on a separate form, not the one with the combo
boxes. I am still fairly new to Access, but my understanding is the "Me"
function will only work with the current form/control/etc.

My [Benefits Cost] box is on my "Margin/Revenue Calculator" form. I have
tried your suggestion, only substituting "Forms![Margin/Revenue Calculator].
[Benefits Cost]" and "Forms![Margin/Revenue Calculator]![Benefits Cost]" in
place of the "Me.[Benefits Cost]", but neither seem to work.

Any other suggestions (other than a sledgehammer)? This is driving me nuts!

But thanks for your help. I am very appreciative of the folks here who take
time out of their day to help people like me achieve objectives.
 
hawk_2001 via AccessMonster.com said:
Gotcha, however I do have one more concern.

My [Benefits Cost] text box is on a separate form, not the one with the combo
boxes. I am still fairly new to Access, but my understanding is the "Me"
function will only work with the current form/control/etc.

My [Benefits Cost] box is on my "Margin/Revenue Calculator" form. I have
tried your suggestion, only substituting "Forms![Margin/Revenue Calculator].
[Benefits Cost]" and "Forms![Margin/Revenue Calculator]![Benefits Cost]" in
place of the "Me.[Benefits Cost]", but neither seem to work.
Click to expand...


Either syntax looks ok to me.

Double check the spelling of the controls and fields. It
looks like you used different names for BenToSalRatio.
 
Back
Top