Group results in Crystal Reports where linked column has multiple related results

Question

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)

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.

I can do this using the code below.

select medications.oid, medications.drug, PMP.action_dte, PMP.actions 
from medications
left join PMP on medications.oid = PMP.fk2_oid

This works fine and i get everything i need. But the medications that have multiple actions are returned once for each action.

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.

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

Where as i would like

 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
       |                   |               |

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.

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.

Thanks in advance!

EDIT ----------

I forgot to mention that there is a date field in the equation. Which is PMP.action_dte. Edited the original query and description.

Apologies for any confusion caused.

EDIT 2 ---------

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.

Answer 1

• Bring in your results
• Under Report > Group Expert, group by Drug.
• Then I deleted Drug in Details
• In Group Header 1, Section Report, check on Underlay Following Section
• Remove Bold for Group #1 Medication

You can download the Crystal Report here