# Top 5 Cust by Cust and an All Other Customers subtotal

#### Ann Ryden

##### New Member
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!

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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,

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.

Replies
4
Views
217
Replies
6
Views
1K
Replies
2
Views
200
Replies
2
Views
410
Replies
3
Views
695

1,203,171
Messages
6,053,878
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back