many-to-many relationship advice

[REBBROWN]

hey all,
I am really hoping someone can help me out on this one since I have been banging my head against the wall for hours.
It's a many-to-many relationship problem that I hope can be resolved in the simplest terms.
I will try and keep from being too long winded here and keep it brief.

I am trying to develop a database for shipping.
The user will first select a carrier (Shipper) and that is one table, we'll call it tbl_CARRIER and here are four typical records for a Carrier:

tbl_CARRIER
AALV
ACOA


APLS
AROF

the Carrier table will have name of Carrier like above for one field and a CARRIER_ID which is primary key, so only two fields in that table, so far so good.

Once a Carrier is selected then they pick a Container Type.
Here are few, actually all records for Container Type:

tbl_CONTAIN_SIZE
40 FT OVER
40 FT UNDER
Doubledrop/RGN
Doubledrop/RGN OOG
Flatbed/Stepdeck
Flatbed/Stepdeck OOG

Same here, an ID autonumber Primary key and name of Container.
I have a one to many relationship set up here because Carrier AALV for example might have all these containers or only 2 or 3.

tbl_CARRIER
carrier_ID Primary Key


tbl_CONTAIN_SIZE
contain_ID
carrier_ID Foreign Key
CONTAINER

Here's where it gets tricky.
There is one additional table, a Mileage table.
There is a Start Miles and End Miles and then a charge per mile.
Here is an example of that table.

START_MILES END_MILES RATE_MILE
51 75 $3.86
76 100 $3.21
101 150 $2.57
151 200 $2.82

The miles go on for a lot of records.
all the way up to like 3000 miles and above

Here's a normal selection.
User picks say ACOA from above and then they pick Flatbed/Stepdeck for a Container size.
Please keep in mind the following;
There are 6 different types of container sizes and each of them have their own mileage rates like above.
The way I have it (in my head) is one Shipper (ACOA for example) selects one of many Container sizes (6 to choose from) and that container size will have it's on rate records like above but ACOA will have different rates per mile for a 40 FT OVER Container than APLS will have for the same 40 FT for the same miles.
does anyone have an idea how to do a many-to-many relationship on all of this?
Any and ALL help will be very much appreciated!
Thanks in Advance.

[June7]

Maybe include a field in Mileage for the carrierID (or use the carrier acronyms you show as the primary key) and have records for each carrier.

Then maybe use dependent comboboxes to limit mileage record choices to the selected carrier. Review http://datapigtechnologies.com/flash...combobox2.html