title: Group results in Crystal Reports where linked column has multiple related results
body: <p>I have two tables that are linked.  The first table is a list of prescribed medications ('medications' table) and the other is a list of actions that relate to the medication when it has been prescribed ('PMP' table)</p>
  
  <p>For each prescribed medication, there can be multiple actions such as authorise, comment, stop etc.
  What i am trying to do is to call all prescribed medications along with every other action for that drug.</p>
  
  <p>I can do this using the code below.</p>
  
  <pre><code>select medications.oid, medications.drug, PMP.action_dte, PMP.actions 
  from medications
  left join PMP on medications.oid = PMP.fk2_oid
  </code></pre>
  
  <p>This works fine and i get everything i need.  But the medications that have multiple actions are returned once for each action.  </p>
  
  <p>My issue comes when i want to put it in to a Crystal Report.
  I have a subreport called Drugs and i want to list all of the authorised drugs on the left and then all the actions for that drug on the right.  But what i get is.</p>
  
  <pre><code>OID  |    MEDICATION      |   ACTION DTE  |    ACTION  
       |                    |               |
   1   | Paracetamol 200mg  |   01.01.17    |  Authorised
       |                    |               |
       |                    |               |
   1   | Paracetamol 200mg  |   03.01.17    |  Comment
       |                    |               |
       |                    |               |
   1   | Paracetamol 200mg  |   10.01.17    |   Stop
       |                    |               |
       |                    |               |
   2   |Ibuprofen 100mg     |   05.01.17    |  Authorised
       |                    |               |
       |                    |               |
   2   |Ibuprofen 100mg     |   06.02.17    |   Comment
  </code></pre>
  
  <p>Where as i would like</p>
  
  <pre><code> OID   |   MEDICATION      |   ACTION DTE  |    ACTION
         |                   |               |
    1    | Paracetamol 200mg |   01.01.17    |  Authorised
         |                   |   03.01.17    |  Comment
         |                   |   10.01.17    |   Stop
         |                   |               |
    2    |  Ibuprofen 100mg  |   05.01.17    |  Authorised
         |                   |   06.02.17    |   Comment
         |                   |               |
  </code></pre>
  
  <p>I have played around with grouping by oid and fk2_oid.  As well as trying to link two sub reports on those fields but i am getting no where.</p>
  
  <p>Is anyone able to suggest a formula or preferably a modification to the code which will allow the crystal report to display the drug on the left ONCE along with every action related to that drug on the right.</p>
  
  <p>Thanks in advance!</p>
  
  <p>EDIT ----------</p>
  
  <p>I forgot to mention that there is a date field in the equation.  Which is PMP.action_dte.  Edited the original query and description.</p>
  
  <p>Apologies for any confusion caused.</p>
  
  <p>EDIT 2 ---------</p>
  
  <p>Apparently my original post was misleading.....modified to hopefully clarify.  I would like the drug grouped by OID with every action allocated to that drug listed along with the action date, as shown above.</p>