How do I add a Discount facility in the Time & Billing Database?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the Time & Billing template to create a database for a service
company. I want to be able to apply a % discount to the 'hours billed' only.
I know what expression to use but am not sure where I should apply it. I have
added a 'Discount' field to the 'Client' table and added this table to the
'Hour by Project' query and modified the 'Billing Amount' expression to
calculate the amount with the discount amount. In the query this results as a
number with +8 digits after the decimal point. The fields in the database
that are automatically updated i.e. the client or project subforms totals,
are blank. What am I doing wrong and where should I place the discount amount
and the expression to calculate the hours to be billed?
 
True,
Your calculated BillingAmount is an unbound calculated field. The
ControlSource is a calculation, and not bound to any field in your table.
But, that's OK, because you shouldn't be saving the result of a
calculation in your table anyway.
Since you are saving Hours and Discount, you can always recalculate the
BillingAmount "on the fly" in any subsequent form, query, or report.
Calculations should be for display only.
Example: For a Discount of .10, a calculated unbound field will "display"
the correct BillingAmount
= Hours * Rate * .90
 
Thank you for replying.
Are you saying that I don't need to modify the 'billing amount' expression
in the ' hours by project' query but to put the expression that you have
given in an unbound box within the form or report?
 
True,
You can put the calculation in the query for the report like this...
BillingAmount : Hours * Rate * Discount
Then BillingAmount can be placed on the report, and can then be
arithmetically operated upon in any footer of that report. Such as...
=Sum(BillingAmount) or =Avg(BillingAmount) etc....
On a form you're only concerned with an unbound field to "display" the
calculated BillingAmount, so just place it on the form like any other
control. No need to have it in the query for the form.
 
Al Camp
Thank you for that...it seems very clear now. Will give it a go tomorrow.
Thanks again
 
Back
Top