Jump to content

One-to-Many Relationship with OR operator?

This topic is 2947 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello friends!

I am building an application for a flight instructor who wants to keep track of the organisation flights.

The database has a table named "PILOTS" (one) and a table named "FLIGHTS". 

The problem i face is as follows:  Any flight has a crew consisted of CAPTAIN and CO-PILOT. Any pilot for "PILOTS" table 

can sit in any of the two positions depending on the certain flight. How do i set a relationship, so that

I can see all the flights from the perspective of one pilot (basically i want the matchfield to be FLIGHTS::CAPTAIN or FLIGHTS::CO-PILOT)



Edited by Dimitrios Fkiaras
Link to comment
Share on other sites

Basically you have two options:

1. Using 3 occurrences of the Pilots table, Pilots, Captain and CoPilot, define these relationships:

Flights::CaptainID = Captain::PilotID

Flights::CoPilotID = CoPilot::PilotID

Pilots::PilotID = Flights::cCrewIDs

where cCrewIDs is a calculation field (result is Text ) = List ( CaptainID ; CoPilotID ).

The problem with this option is that from the context of Pilots, all related flights will appear equally, regardless of the pilot's role in them. Although you could use conditional formatting to distinguish between them, you will not be able to produce a report in the format of:

Pilot A
    Flights as pilot:
        • Flight 001
        • Flight 007
    Flights as co-pilot:
        • Flight 004
        • Flight 005
        • Flight 009
Pilot B

2. Use a join table of Roles between Flights and Pilots.


  • Like 1
Link to comment
Share on other sites

This topic is 2947 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.