AnsweredAssumed Answered

(updated) Multiple "lookups" between two tables / multiple relationships

Question asked by wilz on Nov 29, 2012
Latest reply on Dec 3, 2012 by philmodjunk

Title

(updated) Multiple "lookups" between two tables / multiple relationships

Post

     Im new to DB and FM.  Currently using 30 day trial version to see if this software is right for me.  My knowledge of FM is limited only to the walkthrough file. (which ive completed).  My problem is I dont know how to setup multiple relationships for two fields.  (and apoloiges as im to noob to really know how to write the question properly)

     (Update: since posting i ve learnt about "lookups" and i guess im looking for a way to set up two lookups between two tables) 

     Situation is this: I have two table. 

Flight Table                    Crew Table
     Flight Number                 Pilot Name
     Pilot                                 Pilot WAge
     Pilot Wage
     Origins

     I currently know how to set up a relationship, so when "creating" a flight record, I can enter the pilots name, and it will give me the Pilot Wage

So DATA in my CREW TABLE is
     Record #        1                   2
     Pilot Name   George          Mary
     Pay               $100             $150

Create a Flight:
     Flight Number  : CX103
     Pilot: George
     Pilot Wage: 100 (automatically filled in)
     Origin: Hong Kong

     I RUN into problems when I add in a "co pilot"

Flight Table                    Crew Table
     Flight Number                 Pilot Name
     Pilot                                 Pilot WAge
     Pilot Wage
     Co-Pilot Name
     Co-Pilot Wage
     Origins


THIS is what I WANT to see :
     Create a Flight:
     Flight Number  : CX103
     Pilot: George
     Pilot Wage: 100  <--autimatically entered
     Co-Pilot: Mary
     Co-Pilot Wage: 150 <--automatically entered
     Origin: Hong Kong

THIS is what I END UP seeing :
     Create a Flight:
     Flight Number  : CX103
     Pilot: George
     Pilot Wage: 100
     Co-Pilot: Mary
     Co-Pilot Wage: 100   (THIS SHOULD BE 150, as its looking doenst know how to look for Mary;s wage only Georges)
     Origin: Hong Kong

     SOLUTIONS I dont like:
     I DO NOT want to create TWO Staff tables (A pilot table AND co-pilot table) becuase there may be times Mary will be PILOT and George CO-pilot.  AND for what Im doing I want to see what their respective wages when I create the flight record.

     I'm guessing to do this I have to write a customised script? Or is there a fourmla I can put in? (like a Vlookup in Excel?)

     Any advice (specific or general) would be great.

     Cheers!

Outcomes