Having difficulty with querying service data for a report
I have supporting forms for the following data input, but will try to keep this explanation simple by asking you to assume them even if I don't mention them.
I have a customer who provides four basic services to his clients. Three of the services are charged at a flat fee rate each across the board (they are different, but each is the same for all clients) so we did not include a field in the client data to indicate those charges. The fourth service, though, is charged at a negotiated price to each client and we did create a field to indicate that service's charge in the client data table.
When a service is provided, an entry is made in a services subtable to indicate it, related to the client through the Company ID field.
Now... I need to query this data to then formulate a report to show a total of services provided in a certain time period, and the total income generated. In the query, I know I can write an IIF statement to multiply the number of entries times their flat fee for services 1, 2, and 3. But, how do I also include the total for service 4, when the multiplication has to find the cost of each service on the company info table? Then, of course, I want to add these totals up at the bottom.
Company Info table:
Company ID - key field, related field (one)
Service 4 Charge
Service Autonum - key field
Company ID - related field (many)
The service field will show the names of services 1,2,3 or 4. No indicator is made in the service table on the cost, of course.
What I want in my date-range resulting report is:
Count of each Service
Total Income for each Service
Grand Total Count of all Services
Grand Total Income for all Services
Thanks so much for all your help!!!!
Last edited by vangogh228; 07-28-2002 at 06:41 PM.