title: query multiple tables with columns based on multiple conditions
body: <p>I have three columns: crew info, training info, position info. I am trying to create a report that shows the most recent date of certain trainings that each crew member has taken. Each training should have its own column but some trainings are only required for certain positions.</p>
  
  <p>I have tried a case statement statement as well as just filtering in a where statement but keep getting errors. Some of the research did says that t SQL doesn't have an if statement but some says it does.</p>
  
  <p>Here is the Access code that works: Ballast Water Management: </p>
  
  <pre><code>Max(IIf([CURRENT_FLAG]=1 And [QUALIFICATION_ID] In (14800000086) And [CMC_POS_CODE_TRANSLATOR] In (1,3,6,8,12,13,15,18,20,25,32,90,91,92,94,96,98,100,102,103,230),[ISSUED_ON],IIf([CMC_POS_CODE_TRANSLATOR] Not In (1,3,6,8,12,13,15,18,20,25,32,90,91,92,94,96,98,100,102,103,230),"","0")))
  </code></pre>
  
  <p>What I want is to either show the latest date that a crew member completed the training (if required), a blank if it's not required for them, or a 0 if it's required and they don't have it.</p>