Top 5 Cust by Cust and an All Other Customers subtotal

[Ann Ryden]

I need to create a report that shows the Top 5 Customer's Sales by individual Customer and then shows a Subtotal for All Other Customers.

How should I set up the Queries and Tables to complete the report. Do I need a SubReport? I have never used SubReports.

Example of Report:

Customer:
McKesson $200
Smith $190
Walker $150
Mattsson $140
Wheeler $120
All Other Customers $320

Total All Customers: $1,120

Thanks!

 

[VicRauch]

I would approach it with the attitude of "What do I know, and what do I have to do from what I know to get what I don't know." Sure hope that is at least somewhat clear.
We know (or can get) the top 5 customers with a Top query.
We know (or can get) ALL the customers.
We know (or can get) the total (sales?) for the top 5
We know (or can get) the total (sales?) for all the customers.
Now, looking at this, we can see that the total for all customers except the top 5 is a simple substraction, we just need to get those numbers.

A query of the top 5 for the report with a summary query of All customers to give us the total (sales?) so we can subtract the total of the top 5 to give us the total for all other customers in the report.

Personally, I would do a DLookup("AllCustomerTotal", "qryAllCustomerTotal") to get the total for all the customers.

HTH,

 

[Ann Ryden]

Thanks! I was able to create the report. Next, I have 2 other columns of Current YTD and Prior YTD Returns data to add and a percent calculation of the Top 5 total for all 3 columns.

Your help was greatly appreciated!!