Populating a Table with values from 2 other tables- Pilots and airplanes
I am something of a newbie, but have some experience with RDBMS.
I am building a database app to help keep track of our organizations (Experimental Aircraft Assn) youth flying program. We actually take kids flying for free at rally events. World-wide more than 1.5 million kids so far. This is to be a program to be used at the local chapter level.
In my database, which I am going to use for many events, I have a table listing pilots [id number, name, member number etc.], another table listing aircraft [id number, type, seats, speed, registration etc].
For a given event, at a given place and date, I want to join pilots to aircraft. Some pilots have more than one aircraft, and some rent different aircraft so I cannot just permantly assign an aircraft to a pilot.
So I am planning on a join table, which will contain an ID number, the pilot id, the aircraft id, and the event id. Currently I have relationship's established between the id numbers, not sure if correct types of relations.
Later on I am planning to assign the kids, who have their own table to this join table. In effect saying that on June 15, Peter Jones flew with Pilot John Smith in a Piper Cub because for this event, I have linked Pilot John Smith to a Piper Cub.
At this point I am struggling with the Pilot join to the Aircraft creating the join table records.
What I would like to do is have one layout with dropdown lists of all pilots, another dropdown list of all airplanes, then to select one from each list and then trigger the join, probably with a button running a script, to create the join table records, adding in the event id which I have stored in a global variable.
I am having trouble conceptually figuring out how to do this. Once I figure this part out, the same methodology would probably apply to joining the kids to the aircraft/pilot join table.
Any and all guidance would be appreciated, and my helpers who have to deal with the current paperwork will really appreciate it.
Thanks. BTW, using FM 11 Pro Advanced.