Having difficulty with querying service data for a report

[vangogh228]

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

Services table:
Service Autonum - key field
Date
Company ID - related field (many)
Service

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!!!!

Tom

 

[antomack]

First off I would recommend that you create a table to store the flat rates for the first 3 services. Just a table with Service and Charge is all you need, and enter a record for each of the 3 flat rate services. In this case service matches whatever would be in the service field of your Service table. By having this table it would save time in the event that the flat rates are adjusted. You would then just change the values in the table rather than having them hardcoded in queries and needing to change the details in all queries.

The image below shows a query that would then work behind your report to assign the charges to each of the services.

HTH

 

[vangogh228]

antomack:

Great idea! Establishing the table does simplify everything... and the report gets a WHOLE lot easier!!

It's so odd when you have a mental block that you can't see the answer... and then later it just seems to make so much sense!!

Thanks so much!!

Tom