Pulling a value from a table into a query

[Don]

First let me apologize if I'm in the wrong Discussion Group.

I am working on a database that will calculate commissions for sales people.
I have a query that lists ORDER_SIZE, PROFIT_DOLLARS and MARGIN by customer,
by order date for a date range. When I tried to calculate a column in the
query for my commissions, the conditional statement is so long I run out of
space. I would have had 20 nested formulas in my column and I don't have
enough space to write the whole formula.

Is there any way that I can create a table with the following fields and
then do a database lookup to find the value I want:

MINIMUM_ORDER_SIZE
MAXIMIM_ORDER_SIZE
MINIMUM_MARGIN
MAXIMUM_MARGIN
COMMISSION_PERCENTAGE

I want the query to look at the ORDER_SIZE and MARGIN in the query and find
the appropriate record in the table where these two amounts fall between the
minimum and maximum range and then return the COMMISSION_PERCENTAGE in my
query. I will then use this amount to calculate the commission for this
order.

My main question is can Access do this? Can it look at multiple fields in a
record to determine the value I want to return?

I could write code in a module to do this, but I have a separate issue with
credits where I will have to deduct from the sales people's commissions and I
need to know the COMMISSION_PERCENTAGE on the original sale to know what
percentage to deduct from previous commissions.

Any help would be greatly appreciated. Sorry if I've been confusing.

 

[Jason Lepack]

select
commission_percentage
from
table_name
where
[input an order_size] between
minimum_order_size
and maximum_order_size
and [input a margin] between
minimum_margin
and maximum_margin