Linking master field to multiple child fields

[Mtyetti2]

I am working on an aviation pilot database. I have a pilot form which has a sub form showing which flights that pilot flew. There are usually 2 pilots on any given flight. My two fields on the sub form are "pilot" and "copilot".

My my question is, is there a way for the master field (pilotID) to be linked to multiple child fields? ie: show all records where the pilotID is a "pilot" OR "copilot".



Thanks for for the help!!

[ItsMe]

Sounds like you will need two subforms, one for the pilot and one for the copilot. Another option may, and I emphasize may, be to create a query that has the appropriate join for the pilot vs copilot.

[Mtyetti2]

I was trying to avoid the 2 sub forms if I could avoid it.
Can you explain what you mean by the query to join?

[ItsMe]

Queries use Joins. So if you can forget about the forms and subforms for a minute and go to the query builder..

If you can imagine the relationship or join for the copilot and pilot, then you can build a query based on that. Maybe you will need to combine two queries to get the results you are after, the second query being a subquery. If you can get a row or record to display the way you want it in query view, you can use that SQL for the subform in your main form.