How do I make this calculation

  • Thread starter Thread starter VIRL
  • Start date Start date
V

VIRL

I use access 2003
I have a table named products
ProductID ProductName Unit Price
Residential $15
Commercial $45
On a form I need to make the proper calculation
I need to choose between residential unit price and commercial unit price
residential is $15 for the first 2000 gallons after 2000 it is $2.80 per1000
commercial is $45 for the first 10000 gallons after 10000 it is 2.80 per1000
What formula do I use?
 
You need more information -- delivery dates and what time span is used before
it drops back to the residential unit price?
Maybe it drops back to residential unit price if not deliveries for 4 months.
 
If it's for and based on a single number

this is not code, just the idea

If residential:

if gals <= 2000 is 15 * gals
if gals > 2000 is $3000 + (gals - 2000) *2.8

if commercial:

if gals <= 10000 is 45 * gals
if gals > 10000 is 45000 + (gals - 10000) *2.8
 
I use access 2003
I have a table named products
ProductID ProductName Unit Price
Residential $15
Commercial $45
On a form I need to make the proper calculation
I need to choose between residential unit price and commercial unit price
residential is $15 for the first 2000 gallons after 2000 it is $2.80 per1000
commercial is $45 for the first 10000 gallons after 10000 it is 2.80 per1000
What formula do I use?
Click to expand...

What is the unit being priced? Is it gallon, or is it 1000-gallon? Is $15 a
minimum charge for 2000 gallons, i.e., regardless of whether you use that amount
or not? I am thinking about how a water system might sell water to residential
and commercial customers. Using 2250 gallons might be a charge of 17.80 for a
residential customer. Billing is in 1000-gallon units. Really, you are the
only one who knows how this pricing works.
 
Town sells water to residences
Residential water: $15 for 2000 gallons and under
2.80 per 1000 gallons in excess of 2000 gallons. (4000 gallons would cost
20.60)

Commercial water: $45 FOR 10000 gallons and under
2.80 per 1000 gallons in excess of 10000 (12000 gallons would cost 50.60)

Ps, Im having trouble with the calculation in expression builder
Thanks
 
Town sells water to residences
Residential water: $15 for 2000 gallons and under
2.80 per 1000 gallons in excess of 2000 gallons. (4000 gallons would cost
20.60)

Commercial water: $45 FOR 10000 gallons and under
2.80 per 1000 gallons in excess of 10000 (12000 gallons would cost 50.60)

Ps, Im having trouble with the calculation in expression builder
Thanks
Click to expand...

WaterRates
--------------
product_code
customer_type
basic_charge
basic_usage
unit_price
start_date
stop_date
PRIMARY KEY (product_code, customer_type, basic_charge, unit_price, start_date)

basic_charge + ((actual_usage - basic_usage)/basic_unit) * unit_charge = amt_due

assume actual_usage = 4000 gal.
15.00 + ((4000 - 2000) \ 1000) * 2.80 = 20.60
 
Michael, the info you gave me is perfect, however on a form if I choose the
customer type (residential or commercial), which are two different calcs, how
do I incorporate. If function? (sorry a little new at this)
 
Michael, the info you gave me is perfect, however on a form if I choose the
customer type (residential or commercial), which are two different calcs, how
do I incorporate. If function? (sorry a little new at this)
Click to expand...
First thing is the tables.

You need a Customers table with a column for customer_type, along with
many other columns.

You need a table for meter readings with a column for customer_nbr.

You need a Rates table with all columns necessary to compute the monthly
bill.

You need a query on the Readings table to compute current reading,
previous reading, and the difference.

Now you need a query linking customers with rates using customer_type,
and linking the Readings query using customer_nbr. The amount due is a
calculated column in this query.

Now you make this query the record source of your form and your report.

Customer_type can be a combo box on the Customers table form and the
Rates table form. Customer_type is just a text box on the billing form.

Anyway, this is my take on the problem. If you tables and relationships
are not rock solid, you will have problems. If I were managing a water
utility I would buy a proven off-the-shelf billing system.

Here is the calculation. Rates is a table. Customer Readings is a query
based on Readings table.

[Rates].[Basic_charge] + ((IIF([Customer Readings].[used] < [Rates].
[basic_usage],[Rates].[basic_usage],[Customer Readings].[Used]) -
[Rates].[basic_usage]) / [Rates].[basic_unit]) * [Rates].[unit_price] AS
[Amt Due]
 
Back
Top