Microsoft Access - best way to handle a relationship

I have a database with the follwoing scenario:
A company has shipping rates, to many different ports throughout the world. So there may be 20 companies, with rates to 50 different ports. But these rates can be subject to a haulage charge which is dependent upon which region the end port is in. So a haulage company will charge a different rate if the load is going to Far East or South America. The way I have got around this is to have a table which lists all regions that the haulage company will export to. (Stay with me, I'm getting to the point!) This leads to a huge problem because this table starts to get huge (over a million records). As most haulage companies go to all regions, it would make much more sense to only keep a record of where these haulage companies DON'T export. This would result in a table with probably only 100 or so records.

My problem is - how do I write the SQL to only look for records that aren't there, rather than those that are?

I have attached a sample database, but I have had to hack so much out of it that it may not make sense.
Rick DangerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<My problem is - how do I write the SQL to only look for records that aren't there, rather than those that are?>>

You want what's called an outer join.

 When you do the join between your tables. normally it's an "equi-join" or one in which a record is returned only if you find a match in both tables.

 After making your join, double click on it.   You'll want all records returned from your "main" table (usually option #2).

 Now bring down into the grid the fields from your main table, and the key field from the table your trying to find data in.

 Under that column in the grid, you can but either:

Not is null

 - Which will give you all records where there is data in that table your looking in.


Is Null

 - Which will give you all the records from the main table where there is no matching record in the table your looking in.

 Play around with that with a small example and you'll see what I mean.

Rick DangerAuthor Commented:
Thanks for your answer. I have tried this solution in the past, but didn't get anywhere.
 I have re-attached the database, after changing some of the data.

Looking at the screenshot that I have also supplied, the situation is that, for example,
"haulage_route" 752109 has regions 9, 10 and 11 associated to it,
"haulage_route" 752110 is associated to 10 and 11.

This means that they are the regions they do not go to. So, for each "haulage_route" I need to know which regions they do go to.
In the case of 752109, it goes to regions 1, 2, 3, 4, and 5
In the case of 752110, it goes to 1, 2, 3, 4, 5 and 9.

I need a query that gives me those results.
If the hauling charge for region A is the same regardless of which company and which embarkation point, then the hauling charge table needs only to be by region.  You don't need a row for each combination.  However, if the rate is by company, then you need to use Company and region as the PK.  But unless the rate is different by embarkation point, you don't need the full cross reference that you are building.

You would use a left join when joining to this table since you will not always find a row.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rick DangerAuthor Commented:
I need t know which companies go to which regions. There pricing can be different dependent upon which region it is.

The example supplied may not be that brilliant because I have had to exclude so much, but the point of all of this is that I need to know which regions are not covered by each haulage route.
How many regions?
How many companies?

Companies * Regions = number of rows you need.

PS, some of us can't download databases.  We are not all working at a computer.
Rick DangerAuthor Commented:
Not sure what you are trying to tell me. I'm no expert, but I don't see the relevance of your questions.

Whilst I appreciate your offer of help, if you can't see what I've uploaded I don't think you will be able to help.

Thanks anyway.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry I've been MIA on really busy here.   I'll look at the DB first thing in the morning and see if I can  answer your questions.

You have correct query, but there were some wrong fields. Look at corrected DB
Rick DangerAuthor Commented:
als315 - I can;t work out what you've doen there, but the query you've provided gives the opposite to what I want.

What I need is what I have written above:
In the case of 752109, it goes to regions 1, 2, 3, 4, and 5

In the case of 752110, it goes to 1, 2, 3, 4, 5 and 9.

Thanks for your help.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I'll let Anthony finish up since he's already worked on this.

Rick DangerAuthor Commented:
Please feel free to help! With all due respect to the other kind efforts, we're not close to a solution yet.

I'm grateful for the help, but we're not there yet. If you think you can help, please do.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I'm sure Anthony will get you squared away, but since this has already dragged out thanks to me, I'll work on it if he hasn't gotten back to you within a couple of hours.

Rick DangerAuthor Commented:
That's very kind of you to take the blame! I think that's very unfair - I'm just grateful for the help, even if we do take a couple of wrong turns on the way!
Rick, sorry for misunderstanding. If you like to find routes, missing in you should at first create query with full possible values (qry_Region_Route) and then find missing values (qry_Final)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick DangerAuthor Commented:
Well explained, good solution.

Thanks very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.