Now that I've beaten the weekly summary question I need to create a view (actually I'd settle for any output) that will, by week number, show the sum of service units and the count of clients with open cases during the service period (a week).
This involves three tables: services, clients, and admissions. Getting the sum of services by week number is easy enough (sele sum...group by), but I'm having no luck trying to figure out how to count the records in the clients table for the unduplicated count of open cases for each service week. As an individual query I can COUNT(*) from clients where the client ID is in (Sele the client id from admissions where the client id in (sele the client id from the service table)). I'd like to create this as a view to write a report to which I can pass program and date range parameters. The view would include WEEKNUMBER, SUM(UNITS), COUNT(CLIENTS). I'd appreciate any leads on this as I seem to be adrift this morning. tia Dave
