Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-28-2002, 06:37 PM   #1
vangogh228
Registered User
 
Join Date: Apr 2002
Posts: 302
Thanks: 0
Thanked 0 Times in 0 Posts
vangogh228
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

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


Last edited by vangogh228; 07-28-2002 at 06:41 PM.
vangogh228 is offline   Reply With Quote
Old 07-29-2002, 02:57 AM   #2
antomack
Registered User
 
Join Date: Jan 2002
Location: Ireland
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
antomack is on a distinguished road
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
Attached Images
File Type: jpg servqry.jpg (41.7 KB, 90 views)
antomack is offline   Reply With Quote
Old 07-29-2002, 04:00 PM   #3
vangogh228
Registered User
 
Join Date: Apr 2002
Posts: 302
Thanks: 0
Thanked 0 Times in 0 Posts
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

vangogh228 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 01:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World