trying again at relational for several tables (1 Viewer)

[reb0101]

To anyone who can help with relationship problems;
I am trying yet again to create a Shipping Program here and about to pull my hair out
trying to get the relationships right.
I'll start by explaining what the 'process' is and trying to normalize a spreadsheet with over 8K records in it.

The user will start a shipping record by selecting the departing point and then the arrival point.
Select Region > Country > Port.
This part I have down and have 3 tables with a one to many relationship.
Works just like I want it.
Please see screen shot 1, where I have selected Asia > Indonesia > Jakarta.
This will eventually be used with cascading combo boxes for both sets of values, both having their values pulled to supply both origin of shipment and destination.

Now comes the fun part, selecting a Carrier which is essentially the shipping company.
There are 15 separate Carrier's that could possibly be selected.
Since many carriers could be used for many ports, I had to go with a linking table for this.
If you look at screen shot 2 then you will see that out of the 15 possible Carrier's, only two serve this port;
APLS and MAEU.
The relationships I have to this point are in screen shot 3.

Here's where it gets confusing and I can't even get the relationship structure figured out let alone how to convert all this data and import it in.
(Screen shot 4 is from the excel with remaining fields left.)
Once a Carrier is selected then a Container Size has to be selected, there are 6 potential Container Sizes and some Carrier's have all 6, others only two (or more) as shown in screen shot 4. In this one there is only 2, 40 FT OVER and 40 FT UNDER.
Then comes 'Unit', 6 potential Unit's that like Container Size, can have all, 1 or more to each Carrier.
Looks like 4 of the 6 are in Screen Shot 4.
The final piece here is rate per mile which screen shot 4 only shows very few of.
So Carrier goes to Container Size to Unit to Mileage range with probably 20-25 per unit.
What I am hoping here is that I can have a 'drill down' from beginning to end.
Start with Cascading Combo boxes and eventually cascading subforms.
In the last and final screen shot (5) how can I have a plus (+) from Carrier that will then go down to Container size, then (+) down to Unit and finally (+) down to Mileage Range?
Can this even be done?

Sorry I lied, one more screen shot (6) which is the relationships I have so far that are not working and this is only to the Unit level, haven't even gotten the mileage range into the equation yet.
Any ideas before I have no hair left?


P.S. screen shots 5 and 6 are screen shot 5, hit the maximum with 5 files so cut and pasted 5 into 6 at top.

 

[jdraw]

screen shot 3 only shows part of your tables--What's on the right hand side?

You have Countries and Ports and Carriers -- what is the business?

Before you have screens and buttons, you should have a data model that correctly shows the data involved in your business.

see this tutorial for sample approach
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[spikepl]

I got lost in the mechanics of "have a plus from this to that bla bla ...".

What do you want to be able to do with the data, in plain English, abstain from any db-speak. What is "unit" - in English?

Also, drop formulations like "not working" ("relationships I have so far that are not working"), because only you know what "not working" means. For stuff like "not working" instead state what you wanted, what you did to get it, and what the system delivered instead (incl any error messages).

 

[reb0101]

jdraw and spikepl,
thank you both for the quick replies.
I'll try and explain with more clarity and steer clear of the DB-speak and keep it on a level of more what I am trying to achieve here.
I could very well have the entire table structure wrong here to begin with, so I'll explain without going into what works or does not and any errors, etc.

I will also skip any more screenshots and just go with a 'process' and some sample data in a spreadsheet.

This is going to be a database for shipping stuff from apparently all over the world.
The user will start by selecting where the cargo is originating from; Region, then Country then Port.
First tab in the spreadsheet.
Select where it is coming from by REGION, COUNTRY, PORT and where it is going to.
From the process flow chart this is why these 'feed' both values, ORIGIN and DESTINATION.

It then moves to CARRIER. Carrier is just the shipping company that will actually pick up the cargo, put it on their ship and deliver it.
CARRIER's are on a tab to give you an idea of the data for them.
Once the Carrier is selected then it goes to Container Size, this is the size and type of the physical containers that the cargo goes into.
There seems to be about 6 of them, also on a tab.
The 40 FT OVER and UNDER are pretty self-explanatory I'm not sure what the rest are aside from just other types of shipping containers.

In answer to your question spikepl, regarding UNIT.
the Unit is how it is determined of what rate to charge for that shipment.
there is a unit tab as well.

Per Mile by Container Size
Per Mile for Reefer Containers
Vehicle
Per Mile by Vehicle

here are 4 of them.
As you can see above, there is a per mile for a Vehicle but also a flat rate for just 'Vehicle'.
Per Mile for Container Size as well as Reefer Containers.
The last tab is a very small snapshot of some data.
This shipment originated in Algeria in Africa.
There are several Carrier's listed here but you can see that for AALV with a 40 FT OVER CONTAINER
THE RATE IS $3.58 FOR 0 TO 24 MILES.
When I say this is just a snapshot of the data, that's an understatement.
About 8-9K records.

jdraw you are right about screens and buttons and subforms.
Right now it's 'dissecting' this data and trying to figure out where it will be one-to-many relationships or many-to-many relationships (join tables) and have the user go from step to step and make these selections based on the data they have selected in the step before.
Thanks for any and all knowledge you can share on this one.

 

[Mihail]

Good work in PROCESS sheet.
Except one more thing: From this sheet I don't understand that the rate depend from CARRIER. I see this in your SAMPLE DATA. Have I understand OK ? (The rate depend from the carrier, distance and unit)

 

[Mihail]

While the Countries have unique names on the world, why you need the REGIONS ?

 

[Mihail]

Do you really need StartMiles and EndMiles ? The Distance should be enough.

 

[Mihail]

How the Container Size help you to calculate the Rate ?
From what I see, only the Unit is involved here.

 

[Mihail]

Too many questions until now.
Answer, please, then we'll see.
Add to your SAMPLES one (or more) calculated fields (columns) in order to make us understanding (from yours formulas) what you expect.

 

[gemma-the-husky]

the carrier relationship is not too tricky.

given Region > Country > Port the region/country are immaterial. the only one that matters is port.

either a carrier ships to/from a port or he doesn't. so all you need is a junction table to include carrier-ports.

now what might be an issue is if you try and short circuit this. say a carrier can ship to any port in a given country - then you may be trying to set a carrier-country relationship instead of having to explicitly determine every port, as well as having the carrier-port relationship

if so, then a carrier for a given port should be selected by the union of carriers in the carriers-country table, together with the carriers in the carriers-ports table.

 

[reb0101]

hello Mihail
thanks for your reply, I will try and answer your questions.

Except one more thing: From this sheet I don't understand that the rate depend from CARRIER. I see this in your SAMPLE DATA. Have I understand OK ? (The rate depend from the carrier, distance and unit)

The data was more to show you the fields and what order they came in, and how they relate to one another.
The paste of sample data was not even that accurate.
Yes the rate depends on the Carrier, unit and distance.
I am going to attach a spreadsheet with the mileage rates and you will get an idea.
it's 3900+ records but let me get back to that and get on to your next question.

While the Countries have unique names on the world, why you need the REGIONS ?

the powers that be want to track everything from the top down.
How much did it cost in total for this Region or that Region and It will then move down to Country and Port.
They also want to see how many shipments originate in what Region and how many shipments from say Region Asia to Region Europe or North America.
By the user selecting REGION and then Country and then Port they capture all of this data with each record.

Do you really need StartMiles and EndMiles ? The Distance should be enough.

When you look at the mileage spreadsheet it will hopefully make more sense.
Take a look at the record below.

MAEU Flatbed/Stepdeck Per Mile by Vehicle 2751-3000 $3.50

The start and end ranges start at 0-24, 25-50, 51-75, etc.
Apparently there is a rate change (per mile) by these mileage changes.
The record above is for one Carrier (MAEU) the Container Size is a Flatbed/Stepdeck and it is priced on Per Mile by Vehicle.
The rates seem to cap out at 3K miles so everything over 3K miles is $3.50 per mile.

I will hopefully have a continuous form or maybe list view with a select for the user to choose one of the rates depending on the distance.
If this was web based and my skills were substantially more advanced than they are, it could 'capture' the Port of Loading and the Port of Discharge and calculate the miles automatically.
But this is Access and I'm not at the technological point to develop that so I have to do it the manual way which is this case is to go to http://www.searates.com/reference/portdistance/ and put in the data myself.

I will put in Bangkok, Thailand to Los Angeles, North America.
It comes out to 7739 miles.
The user will at this point obviously ignore any and all shipping rate plans but the final one above that is in excess of 3K miles.
There will be a user input text field, we'll call it 'txtDistance' and then the calculations will be 'txtDistance' multiplied by the rate field, which is this case is $3.50.
$27,086.50.

Once it is all over and done with I believe a lot of these rate plans can be deleted.
What use is a rate plan for 24 to 50 miles?
If it is country to country and especially region to region I believe it will start at 500 miles, maybe slightly lower.

How the Container Size help you to calculate the Rate ?
From what I see, only the Unit is involved here.

Actually it's both.
Carrier then Container Size, then Unit.
You can have one Container Size, say 40 FT OVER and for that one container size, there can be at least 2 Units, Reefer Container and Per Mile by Container Size.
As you can see from the Mileage spreadsheet, a lot needs to be cleaned up and clarified.
At this point I just need to set up the 'flow' and hopefully load only what mileage rates and distances that are needed.
It all comes down to 'drilling down' at this point, Select the Carrier, the Container size, and then the Unit.

gemma-the-husky,
I was writing this reply offline and just saw your input, so I'll reply to it as well.

the carrier relationship is not too tricky.
given Region > Country > Port the region/country are immaterial. the only one that matters is port.
either a carrier ships to/from a port or he doesn't. so all you need is a junction table to include carrier-ports.

Absolutely, and this is exactly what I have done, so far.

now what might be an issue is if you try and short circuit this. say a carrier can ship to any port in a given country - then you may be trying to set a carrier-country relationship instead of having to explicitly determine every port, as well as having the carrier-port relationship
if so, then a carrier for a given port should be selected by the union of carriers in the carriers-country table, together with the carriers in the carriers-ports table.

I had to read this a couple of times and will probably have to do so a third time.
You are ahead of me here but It's not something I have not already thought about when (and if) it gets to the point of the actual forms the user will be using.
A Carrier may ship to and from a particular country, but it does not mean it will ship to and from every port in that country.
For that reason if a user needs to add, edit or delete a Carrier it has to be on the Port level, not country or even Region.
To add edit or delete they have to select Region > Country > and then Port to add edit or delete Carrier.

I am going to add one screen shot here to hopefully give you an idea of where I am, and where I hope to be.
This is a screen shot of the tables and how they relate to each other so far.
The first one, and you will see red lines, is the Region of Asia Selected.
When I click the "+" it expands out to show all of Asia (Countries) and I then select India.
I click the "+" again and it expands out to show ports in India.
When I click the "+" one final time, it shows the available carriers which it is pulling from junction table PORT_CARRIER.

At this point in the process the next step is Container Size and then Unit and then the Mileage rates.
So given my screen shot, I need another "+" where indicated in the screen shot.
one-to-one, many-to-one?
I am going to keep banging away at it for now but thanks for all of your input so far guys.

 

[Mihail]

Yep. Seems that you clarified some aspects.
I am too tired now so, I'll read this again tomorrow.

But, what I see from your screenshot, is that you try to manage the database at the table level.
That say to me that you have a long experience in Excel development.
You can forget almost all this experience . Keep only the VBA skills.

Your database can NOT be managed at table level.
You MUST manage it by using Forms (I think that you know what are this from Excel) and specific tools for databases. The main tool is the Query (or SQL - it is almost the same thing). Other good tools are the "aggregate functions".

As I say, tomorrow, in the morning, I'll take a "fresh" look to this.
Bye !

 

[gemma-the-husky]

i agree 100% with Mihail.

forget the plus signs in the tables. design your tables to have appropriate relationships, and then design forms and reports to present data.

no one should ever need to open a table directly. (maybe the developer when trouble shooting - but there should not really be any need to use tables directly - which is why the plus signs are pretty much pointless)

 

[Mihail]

Something is wrong in MILEAGE_RATE.
Apart that there are duplicates (fully duplicates: same Carrier, Container_Size ... Rate) you have partial duplicates that annoying me: Same Carrier, Container_Size, Unit, Start, End but... different RATE.
Why ? Seems that the Rate value has more dependencies than are shown in your spreadsheet.

 

[reb0101]

Mihail,
Don't bother with the mileage rates, they are as you said incorrect with lots of duplicates and other errors.
I was given a spreadsheet with a lot of tabs and over 8K records, Mileage Rates was just one of those tabs.
my 'task', if you want to call it that is to go through this scattered data and try and assemble a way to create a program to track shipments.
A lot of the data was fine, 'as is'.
The REGIONS, COUNTRIES and PORTS is fine and I was able to assemble a one-to-many relationship that works fine.
After PORTS then they select a CARRIER.
This is the roadblock I have hit not only in the design process (for table relationship and design) but as of last night, for forms as well.
PORTS and CARRIERS uses a junction which is my current pain for cascading combo boxes.
Once CARRIER is selected then CONTAINER SIZE, UNIT and then and only then, mileage rates.
Someone not only loaded all the rates as you see, but duplicated many and created other errors as well.
When I get to that point I am perfectly happy giving them the 'function' and they fill in the values.
As I pointed out yesterday, I doubt very seriously that a 0 to 24 mile rate is ever going to be applicable.
Getting a related table from CONTAINER SIZE to UNIT and then down to rates is more than sufficient.
I will give them the ability to input those miles and rates but they can input them themselves.
Why go to teh trouble to try and load all that data into a table or tables if we already know it's all wrong.

 

[Mihail]

Uh. Seems that we have all information to design the database. I'll design this database for you then we'll try to make it functionally by creating forms to manipulate data. I'll start now, but I think that i can accomplish this in one or two days.

 

[reb0101]

Thank you Mihail,
I'll zip and attach where I got before I more on to the more frustrating issue of cascading combo boxes with junction tables and having text boxes display selected data that apparently won't display as text (and displays as ID number) unless you change them to a combo box.
I'm getting less and less excited about lookup fields by the hour.

 

[Mihail]

Ok. I think that must be your database. Of course in I don't misunderstand something. If so, inform me.
You are welcome with any question.