How to assign a range of numbers for a specific data

[splashy]

Hi there!
I am trying to create a database for calculating costs of shipping an item depending on the postcode. Here is a picture that explains what I am trying to create: http://i.imgur.com/VrnBkTE.png

So as you can see, in region N1, the cost is $9.90 and covers the postcodes between 1000-2000, 2030, 2050-2070. I want to assign these values to the region N1 with the cost $9.90 so that when I look it up on the query, it shows the correct price. For example, if I were to enter 3502, I would expect it to show that it is in region N2 with a cost of $15.30.

I cannot figure out how to enter a data range to make this easy. Any help would be greatly appreciated!

[twinnyfo]

splashy,

What you are asking is somewhat simple, but will require you to build a table to hold all your regions.

Your Table should have three fields:

tblShippingZone s

PostCodeStart The lowest value of the post code in this particular list
PostCodeStop The highest value of the post code in this particular list
ShippingRegion Foreign key to tblShippingRegi ons

tblShippingRegi ons will have three fields: One for the RegionID, one for the Name of the Region ("N1", "N2", etc.) and one for the Shipping cost to that region.

So, for example you give above in your pic, you would have 7 records:

PostCodeStart PostCodeStop ShippingRegion
1000 2000 N1
2030 2030 N1
2050 2070 N1
3000 3450 N2
3456 3456 N2
3487 3487 N2
3500 3600 N2

Then, when you want to find shipping rates, just compare the post code to the values in the table and when the post code is between the start and stop codes, then use the Shipping region.

Hope this makes sense.