I have a table of prescriptions, indexed by patient number and a running number for each patient. The row includes medication name, start date and end date. I want a report counting the number of patients getting each kind of medication within a given time period. The user selects the date range in an Access form, which opens a report with a criterion using a call to my date overlap function. The report is grouped by medication, with the detail empty and the group footer showing medication nameand count of patients. This works… except that sometimes a patient has a medication repeated within the time period the user specifies, and then I find myself counting prescriptions, not patients. I have tried various SQL tricks in place of havingmerely the table as data source. I cannot SELECT DISTINCT because OpenForm needs the dates for its own criterion. I am willing to change the report’s internal format.
