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