One-to-Many Relationship with OR operator?

[Dimitrios Fkiaras]

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)

Thanks!

 

Edited February 8, 2016 by Dimitrios Fkiaras

[comment]

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.

 

[Dimitrios Fkiaras]

23 hours ago, comment said:

Thank you!