2 Replies Latest reply on Jun 24, 2013 2:31 PM by kraftfm

    Report Design - Potentially Use of Sub-Summary?

    kraftfm

      The application I am working on is for an alternative medicine counselor. The four-table solution is designed much like a classic invoice solution as:

       

      Client = Customer

      Consultation = Invoice

      Medication = Invoice Line Item (you can get multiple medicines per consultation)

      Pharmacy (medicine name and description table) = Products

       

      It is a standard configuration with Client linked to Consultation and Medication linked to both Consultation and Pharmacy so there are just one-to-many relationships. The relationship graph is as follows:

      Table Relationships.png

       

      My issue is with creating a report for the user. What has been requested is to see for a given client, and within a given date range, the medicine and other pertinent data (e.g. potency, frequency of usage, consultation date, etc.). The report scheme that I created is as follows. On a separate screen I request the user to enter a from-to date and display the results in this format.

      Print_Client_Medicine_Summary.png

       

      This report works, however the user now wants to show a summary field (summary consultation notes, field ConsultSum) for each new Consult. Therefore you would have the summary going across the page (once) and listed underneath it the Medicine detail, as much as there is per Consult, as I have it in the current report.

       

      I have thought of using a Sub-Summary report when sorted by the foreign Consult ID field in the Medicine table (the current report layout is based on the Medicine table). I have tried this and it doesn’t work. ConsultSum is housed in the Consult table.

       

      Input/thoughts on how I can address this problem?