query on airlines

[rekha]

Hello, please explain......

These are the 3 tables AND THE FEILDS that I have:
1. Airline - AIRLINE ID, AIRLINE NAME
2. Destination - DESTID,DESTCODE,DESTNAME,AIRLINEID
3. Rate - AIRLINEI ID,AL NAME,DEST CODE, CATG1, CATG2, CATG3.....CAT6, CATGID

I am able to accept destination from the user and display records based on
the accepted dest:
ex: when user enters JFK, all records pertaining to that destination is
displayed.

The problem is here:
I want to accept weight or category from user, based on the weight /
category, records with all details needs to be displayed but all the rates
stored under catg1, catg2..catg6 should not be displayed.

When the user inputs 1 for category, then only rate stored under catg1 for
this particular dest should be displayed similarly when user enters 5, rate
under catg5 should be displayed and not all the rates.

The above query should work along with the dest query..

can you help pls?

Tks/Rekha

 

[Tom Ellison]

Dear Rekha:

I think I see numerous problems, including the cause of your immediate
issue, that are coming from the way the tables are designed. It is
issues with the fundamental design of the database, that is, the
tables, that underlie the problems you now face, and will continue to
experience.

I would recommend your tables be more like this;

Airline: AirlineName
Destination: DestCode, DestName
Category: CategoryNumber
Rate: AirlineName, DestCode, Category, Rate

Notes:

I have omitted using any IDs. Add them if you feel you must. I don't
think they're going to do much for you. This is a potentially long
discussion. But you will need a unique index on AirlineName even if
you use the ID. You wouldn't want two rows in Airline with the same
name. That could spoil everything. If AirlineName is unique, then
the name can just be the key. It's not like you're going to have tens
of thousands of airlines, right?

Are destinations actually dependent on which airline goes there?
Isn't JFK the same destination no matter which airline gets you there?
If so, don't put AirlineID in this table, or AirlineName either.

I added a category table. You may think of this as something static,
and you may be right. But it is a definite dimension in what is
happening. You really need a table.

Now for the change that will bend your mind - and will ultimately
change your thinking about database design (I hope!) The three tables
to this point are Airline/Destination/Category. They work together to
form a "matrix". For every airline, for each destination, and in each
category, you have a rate. If you have 5 airlines, 10 destinations,
and 6 categories, then you have 5 X 10 X 6 = 300 possible rates
(assuming all airlines go to every destination using all the possible
categories). The rate table should key by these 3 elements and give
you the rate.

I'd like to stop now and let you digest. Can you see why this is a
step in the right direction?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts