AnsweredAssumed Answered

Report Design - Potentially Use of Sub-Summary?

Question asked by kraftfm on Jun 24, 2013
Latest reply on Jun 24, 2013 by 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.



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?