I am building a query for my 'pilot' database. The basic outline is an 'Event' table which stores the flight info. (date, flight time, location, and up to 2 pilots).
I have created a search form so I can search the Events under several different criteria. However, in my query design, each time I add the Pilot table, the query returns no results. I think it has something to do with the fact that the Pilot table has multiple relationships to the Event table, but I don't know how to get around this.
The Pilot table is linked (with an ID field) to the Event table. There are 2 relationships; (one for captain and one for co-captain of the flight linked to the EventID). Ideally, I would like to be able to search the pilot, and have the query return all Events where that pilot was the captain or the co-captain. Thoughts???
Thank you all so much
Scott