Calculation problem

[Joe]

Hi, all,

I am a newbie of Ms Access.

My boss needs me to develop a small-scaled Ms Access 2002 application that
to store and do some calculation of the cost (printing).

That means I provide a Form for user to input records (printing material,
vendor, customer, jobs¡K).

Because there are different combinations of calculation method, such as
print 1-500 pages for $A, print 501-1000 for $A * 1.8 ¡K¡K.Also different
price for color and black and etc.

So I don¡¦t want to hardcode the equation in the query, I had use a table to
store the equation. Following are the table structure


Table: BASE_PRICE

Category | Price
===========
A | 10
B | 20


Table: EQUATION

From | To | multiple
===============
1 | 500 | 1
501 | 1000 | 1.8
1001 | 1500 | 2.7

I want to let user to input the no. of page and category in Form and then
the application search the equation & base_price table that it within which
group and do calculation.

For example, if user select category [A] and input 550 for pages, so it
means $10 * 1.8 = $18.

So I want to know that can Ms Access do that?

Thx for your attention !

Joe

 

[Marshall Barton]

My boss needs me to develop a small-scaled Ms Access 2002 application that
to store and do some calculation of the cost (printing).

That means I provide a Form for user to input records (printing material,
vendor, customer, jobs¡K).

Because there are different combinations of calculation method, such as
print 1-500 pages for $A, print 501-1000 for $A * 1.8 ¡K¡K.Also different
price for color and black and etc.

So I don¡¦t want to hardcode the equation in the query, I had use a table to
store the equation. Following are the table structure


Table: BASE_PRICE

Category | Price
===========
A | 10
B | 20


Table: EQUATION

From | To | multiple
===============
1 | 500 | 1
501 | 1000 | 1.8
1001 | 1500 | 2.7

I want to let user to input the no. of page and category in Form and then
the application search the equation & base_price table that it within which
group and do calculation.

For example, if user select category [A] and input 550 for pages, so it
means $10 * 1.8 = $18.

Here's a top of the head expression to do that.

DLookup("Price", "BASE_PRINCE", "Category = '" & txtCategory
& "' ") * DLookup("multiple", "Equation", "From <= " &
txtPages & " And To >= " & txtPages)

where txtCategory and txtPages are the text boxes on the
form where the user entered the values.

While that should do what you want in a one time calculation
on a form, it would be inappropriate to use that in a
complicated query that calculated the rate for a lot of
records all at one time. So, if you get to that point, post
back with more details about the situation(s?) where you
need to do this kind of thing and we'll see what we can come
up with.