Tables and Relationships, I'm stumped!

[REBBROWN]

Hey guys, I am hoping someone can help me out here.
Sorry for the length, I will keep it as short as I can, but will still probably still be pretty detailed.
I am trying to create a shipping program.
I have most of the technical aspects with Access down, but get lost trying to get my head around the relational aspects.
This is what I have so far and as I describe the tables, I need help figuring out how to relate one to another and ‘drill down’ as a selection process goes, so here goes.

1^st table is “REGIONS:
not many records in this table, as a matter of fact, here they are:

AFRICA

ALASKA, HAWAII, GUAM

ASIA

EUROPE

MIDDLE EAST

NORTH AMERICA

OCEANIA

RUSSIA AND CIS

By starting with the process, through possibly a drop down the user will select one of these regions.
That will then take them to the country table.
By selecting say “Middle East, the country table has 55-60 records in it, but it will isolate on the countries in the Middle East.


So, REGIONS table, and COUNTRY table so far.
Then there is the PORTS table.
there are 136 Records in the PORTS table, and hopefully by selecting the Middle East then Countries in the middle east, it will Isolate only the ports in that country that is selected.
So far the shipment has been created from the WHERE point, I am hoping I can also pull the TO from these tables.
Shipment Starts in the Middle East but is delivered in the United States.
At some point in this process, I have a ROUTE table.
Route Table has 55 records, here are 2-3 of them so you can see how it is worded:

04 - Middle East, South Asia, Indian Ocean Interport

05 - U.S. East Coast - Continental Europe, United Kingdom, Ireland

06 - U.S. East Coast - Mediterranean

I don’t believe the ROUTE table is that ‘hard coded’ I just need a working program and they can alter these.


Here’s where it will start to get complicated:
There’s a SHIPPERS table.
currently 15 records in that table.
As you drill down, from REGION, to COUNTRY, to ROUTE to PORT, it has to take in into consideration these Shippers (records). Say you select MIDDLE EAST but only 12 of the 15 Shippers work in the Middle East.
Then you click say Kuwait for Country and now you are down to 09 shippers.
So there is that additional complication.


I know in theory that it’s as simple as ‘assigning’ a shipper through some type of a relationship to each step.
Shipper 01 works in AFRICA, but doesn’t in The Middle East, etc.

Then comes the UNIT Table. There are only 7 records in this table and one of them has to be selected:

Conveyance

Measurement Ton

Piece

Reefer Container

TransPack (RLB)

Vehicle

Then after UNIT Table MILEAGE_BAND.
These bands and CONTAINER_SIZE determine the shipping rate.
Here is an example of a couple of records in the MILEAGE_BAND table:

0 to 24 miles

25 to 50 miles

There are 26 records in the MILEAGE_BAND table
Once MILEAGE_BAND is selected then one last selection, from the CONTAINER_SIZE Table.
Here is an example of a CONTAINER_SIZE Record.

40 feet and over

Under 40 feet

There are 6-8 Records in the CONTAINER_SIZE Table.
Once this process is ran through, the Selections made from each table, then a rate is assigned:
Here are three Shippers and their rates:

APLS	$319
MAEU	$350
AALV	$358

If you have made it this far then thank you.
Each of these tables only has one Field until I figure out how if possible to consolidate them.
All of this data has come from an Excel spreadsheet that I have imported into the tables that I described.
Any and all help will be very much appreciated.

raymondebrown@gmail.com

[orange]

As a first suggestion, and in my opinion the way to approach any database application, provide a 3 -4 line overview of what the application is about. Then get a top level picture of the things you are dealing with. For your own benefit for documentation, clarity and communications, write a 2 line description of what those things are. You'll be surprised at how much this little bit of documentation can help determine what is/what is not represented in the database, and how one thing differs from all others.

Next will be to identify the business rules -- loosely - how these things relate to each other in business terms.
My advice is not to get too involved with Forms and details.

The key to successful database is proper design to meet the business need. Typically that means normalized structures.

Here is a link to one approach to database design. http://www.databaseanswers.org/approach2db_design.htm