We help IT Professionals succeed at work.

Calculated Field - in table?

pdvsa
pdvsa asked
on
Medium Priority
395 Views
Last Modified: 2013-11-29
Experts,

i need to build a table of Commissions.
The commission amount is a calculated field.
I dont believe I can put the calculated amount in the table.

But how can I put that same amount on a report?  
Do I have to also calculate that commission on the report as well?    
I would prefer for that calculated commission amount to be in the table
Any quidance will help.
I dont know the recommended practice.


thanks
Comment
Watch Question

Commented:
Put the commission calculation in a public function. Pass the necessary data to the function and make the function return the calculated value. Call the function in any query, form, or report that needs it.

Lee
pdvsaProject finance

Author

Commented:
OK so I see that typically you dont save calculated fields in a table...usually it is done in a function.  

thank you

Commented:
Right. If it's a function, you only need to create the calculation once, but you can use it anywhere you need it. Also, if you need to change the calculation, you only need to change it once.
Are you using Access 2010?

Ed
CERTIFIED EXPERT
Commented:
Some words about practice: if your calculations are complicated and took many time, you can store them in your table (it is possible, for example, with update query), other way you shouldn't store them.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
<other way you shouldn't store them. >

Another big exception is if the calculation is of "historical significance".  
pdvsaProject finance

Author

Commented:
Ok.... Thanks.  I am using 2007.  Had internet issues....  The calculation is very simple: amount * rate.

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
If those two values are in the reports underlying recordsource, you can set the control source property of a textbox as follows (include the = sign):

= [amount] * [Rate]

If they are not in the report's recordsource, where/how should they be 'looked up'?
Commented:
I agree with the 2 suggested exceptions, long time to run and historical significance, for potentially storing calculated data in tables. You must remember to rerun the calcs when appropriate.

In this case though, even as simple as this appears, I would still code it as a function for the reasons I said above. If you have this simple calculation in several queries and in a few forms and reports AND the calculation ever changes, you now must change it everywhere instead of just once. If you miss one, your data to your users appears unreliable.

Example:
Current:      amount * rate
Tomorrow:  amount * rate * 1.05

Add any error handling and change your data types where appropriate.
Public Function CalculateCommission(amount As Single, rate As Single) As Single

  CalculateCommission = amount * rate

End Function

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.