Find shipping charges based on order size & State

[Guest]

I'm pretty new to access, so forgive me if this is a kindergarten question.
I can't quite figure how to fit this last piece of my database in.

I have my customers in a table, columns of: Name, address, state, total of
order, etc.

I can't quite figure out how to have accese determine the shipping charge
when it is dependent on two things, 1. the amount of the order, and 2. the
state

My table would be something like this:

Shipping Charges
Amount of Order
State: $0-50 50-100 100-150 150-200 over 200
NH 5.00 7.50 9.00 10.00 0
VT 5.00 7.50 9.00 10.00 0
MA 6.00 9.00 12.00 150.00 0

How would I get access to choose the correct shipping base on the state and
order total?

I am no using a form, just tables and queries.

Thanks in advance

 

[Larry Daugherty]

The only things that belong in the Customer table are attributes of
customer that are not a function of anything else: Order size doesn't
belong here.

You need another table to record the Orders. If your order can
consist of more than one item then you'll also need another table for
the OrderDetails. Look at the sample Northwind.MDB that comes with
Access. It has tables of all of those names. The Northwind example
is built to show off various features of Access rather than to show a
nifty completed application.

You'd calculate the shipping charge from information down at the
lowest level and from the customer's Ship To address.

If you want your application to serve you faithfully and well then
you'd better go back and design some Forms and Reports. You should
never be required to get directly into the tables to massage the data.

HTH

 

[Van T. Dinh]

You should break the "Amount of order" to 2 Numeric Fields: OrderAmountMin
and OrderAmountMax (some others will disagree with this). Your "Look-up"
Table should have the following Fields (minimum)

State
OrderAmountMin
OrderAmountMax
ShippingCharge

Then you can use DLookUp() function (or other methods like Recordset) to
retrieve the ShipingCharge when the OrderAmount and Destination are known.

Check Access Help / Access VB Help on the DLookUp() function.