title: Generating column from values coded in another table
body: <p>I'm trying to create (what I thought was) a simple Crystal Report. Essentially, I want to produce a report that lists, for instance, each time a student has received some kind of disciplinary action:</p>
  
  <pre><code>╔═══════════╦═════════════╦════════════╦══════════╗
  ║ StudentID ║ StudentName ║   Action   ║   Date   ║
  ╠═══════════╬═════════════╬════════════╬══════════╣
  ║ 1         ║ Smith, John ║ Suspension ║ 1/1/2012 ║
  ║ 2         ║ Q, Susie    ║ Detention  ║ 1/5/2012 ║
  ║ ...       ║ ...         ║ ...        ║          ║
  ╚═══════════╩═════════════╩════════════╩══════════╝
  </code></pre>
  
  <p>However, this information is stored in the database as three separate tables:</p>
  
  <ol>
  <li>Student information, such as their ID and name.</li>
  <li>Disciplinary actions, including the student's ID, date, and a code indicating what kind of action was taken.</li>
  <li>A table that pairs each code with a description of the action, so 1 maps to "Detention", 2 maps to "Suspension", etc.</li>
  </ol>
  
  <p>I am not sure how to link these columns in Crystal Reports, specifically so that I can display a column describing the action taken rather than just the code. I thought that a SQL Expression field could accomplish this, like so:</p>
  
  <pre><code>(
  SELECT "ActionCodes"."ActionDesc"
  WHERE "DisciplinaryActions"."ActionCode" = "ActionCodes"."ActionCode"
  )
  </code></pre>
  
  <p>Adding this field to my report only produces blank rows, unfortunately. Am I on the right track and am just fumbling with my query, or is there a better route?</p>