Flat Shipping Rates

[innovations2]

What would be the best way to design a table for flat rate shipping based on order total value?

The shipping rates would be something like this:
From $1-20.00 = $3.50; $20.01-50.00 = 5.95; $50.01-75.00 = $7.95 etc. etc.

I am designing an eCommerce website and I need to create some tables in my database for shipping and taxes. I think I have the taxes figured out, but not the shipping. As you can see above, I need the shipping rate to be based on the order total.

Thanks for you help!

-Aaron

 

[Remou]

If you set up your table with upper and lower boundries for your rate, you can use them to determine the shipping rate. For example:
[tt]DLookUp("Rate","tblShipping",[txtPurchase] & " between PurchaseLower And PurchaseUpper")[/tt]
Or
[tt]strSQL="Select * from tblShipping Where " & [txtPurchase] & " between PurchaseLower And PurchaseUpper"[/tt]

 

[innovations2]

I'm a little intimadated now... I'm not familiar upper and lower bounderies.

Are you saying that I should have the following table structure:

tblShippingRates
=====================
rate_ID (autonumber/primary)
rate_Lower (min total)
rate_Upper (max total)
rate_Price (currency/cost of shipping)

That seems like it might work. Then would I just have to use a SQL statement like yours above? Something like:

SELECT *
FROM tblShippingRates
WHERE [totalprice]
BETWEEN tblShippingRates.rate_Lower
AND tblShippingRates.rate_Upper;

Is that correct?

 

[Remou]

Yes, I thought something like that should work for you.

 

[innovations2]

Great! You really spurned me on and put me in the right direction.

Thank you very much!

-Aaron

 

[Remou]

Oops, I am sure you know this, but perhaps for other readers, make sure you do not overlap:

[tt]RateID Rate_Lower Rate_Upper
1 1 10
2 11 20 'Not 10, 20[/tt]