Need help creating "Top 10" report/chart

[Brlee1crv]

Greetings,
I have a few tables that keep track of how much business is coming from each client. I have a query set up which counts the number of jobs each client has given year to date.

My problem is this: I have about 50 clients I would like to display on a report (particularly on a pie chart with percentages). Since this number would be too many to show on a single pie chart I want to display the top 5 producers with their percentage of jobs and the rest to be grouped and displayed as "Other" on the pie chart. Is this possible?

 

[dhookom]

This is possible but a bit complex with subqueries etc. The main problem is that we have no idea what your fields or tables are named, some sample data, and how you would define "top 5 producers".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Brlee1crv]

Thanks for the reply. I have two tables (tblClients and tblJobs). tblClients contain client info such as CliName, CliAddress, etc. tblJobs has JobNo and DueDate.

The query I have for the report counts the JobNo using DueDate as a parameter. Criteria for the DueDate is:
>=01/01/04 and <=Date()+30
The query also pulls the Client name.

Now this works fine when I run the chart wizard and display the results but I get a mess of a pie chart with percentages and colors because there are so many clients.

So to clear up the mess a bit I'd like to display the top five clients who have the most jobs and group everyone else as "Other" on the pie chart. Do you think this would be more trouble than it's worth?

 

[dhookom]

Assuming you have a totals query "qtotClientJobs" with fields ClientId and CountJobs. You could create a query like:
SELECT IIf([tblClients].[ClientID] In (Select TOP 5 ClientID FROM qtotClientJobs ORDER BY CountJobs Desc),[tblClients].[ClientID],"Others") AS TheClient, qtotClientJobs.CountJobs
FROM qtotClientJobs INNER JOIN tblClients ON qtotClientJobs.ClientID = tblClients.ClientID;
This will return the ClientID for all the top five and "others" for all the others. I didn't have time to correct the group by error when you attempt to group by theClient.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.