title: SELECT Query where only one of three fields containing Foreign Keys is filled
body: <p>As shown below the Table <code>Recording</code> contains three foreign keys, only one of which will be filled for any one entry. My question is how can I format a select statement in Access that will only pull the names from the relevant table given the foreign key for that table?</p>
  
  <p>I have tried using IIf, which works when checking which Foreign Key is Not Null after the SELECT:</p>
  
  <pre><code>SELECT Recording.[idRecording],
         IIf(Recording.[Artist_idArtist] Is Not Null,  
               Artist.[artName] , 
                   IIf(Recording.[Band_idBand] is Not Null, 
                        Band.[bName], 
                          Composer.[cName]))
  FROM ...
  </code></pre>
  
  <p>But putting any conditions after the FROM statement, to get the correct JOIN, results in an error:</p>
  
  <pre><code>FROM 
     IIf(Recording.[Artist_idArtist] Is Not Null, 
           Artist INNER JOIN Recording ON Recording.[Artist_idArtist] = Artist.[idArtist],
              IIf(Recording.Band_idBand Is Not Null,
                    Band INNER JOIN Recording ON Recording.[Band_idBand] = Band.[idBand],
                      Composer INNER JOIN Recording ON Recording.[Composer_idComposer] = Composer.[idComposer]));
  </code></pre>
  
  <p>I'm new to this so I'm probably missing something obvious, or going about it the wrong way. I believe what I've ended up with here is an Exclusive Arc? I'm not sure if this is even a good design, I had thought of scrapping the Recording table and simply adding the foreign keys to Track.</p>
  
  <p>For reference here is the Relationship Design:</p>
  
  <p><img src="https://i.stack.imgur.com/LMHag.png" alt="ER"></p>