Question Two fields, one table design?

[karl009]

Hi,

I have a question about the design of tables and relationships...

What’s going on is I have two tables, one is say Flights, and the other is Airports.

I have a departure_airport field and a arrival_airport field, I have a one-to-many relationship on the departure_airport field, what’s the best way to link the arrival airport field.

Many Thanks
Karl

 

[stopher]

It will be another one to many relationship. However, you must add the airport table to the relationship view a second time. This will show a second airport table but with a slightly different name (alias). You can then create the relationship to the alias.

hth
Chris

 

[karl009]

Hi,

I have done this, when I add the second relationship the first will break.

When I open up the Airport table and click on the records because of the first relation I am able to see all the flights related to that airport, but I lose that when I add in the second relation for the arrival.

Thanks
Karl

 

[stopher]

See my example (attached). Take a look at the relationship view.

Chris

 

[karl009]

I see what you mean, if you look at my screenshots you will see that within the tblAirports there are no related records for Flights yet if there is only a link on the departure_airport field then the records are seen.

The screenshots will explain better...

To me this breaks the relationships as it does not know which one to look against.

Thanks
Karl

 

[stopher]

I suspect the table view is only a very basic view and hence can't present the relationship on screen. Don't worry about what the table view shows. Users should never see the table view in any case. I never use it at all.

Chris

 

[karl009]

Hi,

Thanks for your help...

After playing around I see that you are right, queries just need a bit of extra work in the direction you talked about.

Thanks
Karl