Prev: Publishing a report to PDF
Next: Add the word "continued" in a Repeat Section
From: averied on 7 Sep 2009 07:26
Hi, I have inserted a pie chart Access 2007 Report using the MSGraph.Chart.8
OLE control class ..

This chart takes the information from a query with to colums, One is the
Employee Name and the other column has the number of sales per Year.

The chart I want to display is called Top Sales employees. The chart should
display the name of the TOP 5 employees and the percetage that their number
of sales represent in the total sales of the year. The rest of employees
should be grouped as "Other", and Others must display the percentage of the
total sales minus the TOP 5 ones..

So my idea is to limit the Pie Chart to 6 slices, so the top 5 slices will
show and the rest will be grouped, but I can't find how to to this.

PLease, can someone help me achieve this? Thanks..
From: Duane Hookom on 8 Sep 2009 21:02
I would first create a query of the top 5 employee sales. €
-- qselTop5 ----
SELECT TOP 5 Employee
FROM tblEmployeeSales
ORDER BY NumOfSales DESC;

Then create another query with this query and the table using a LEFT JOIN:
--- qtotForGraph ---
SELECT Nz([qselTop5].[Employee],"other") AS Expr1,
Sum(tblEmployeeSales.NumOfSales) AS SumOfNumOfSales
FROM tblEmployeeSales LEFT JOIN qselTop5 ON
tblEmployeeSales.Employee = qselTop5.Employee
GROUP BY Nz([qselTop5].[Employee],"other");

--
Duane Hookom
Microsoft Access MVP


"averied" wrote:

> Hi, I have inserted a pie chart Access 2007 Report using the MSGraph.Chart.8
> OLE control class ..
>
> This chart takes the information from a query with to colums, One is the
> Employee Name and the other column has the number of sales per Year.
>
> The chart I want to display is called Top Sales employees. The chart should
> display the name of the TOP 5 employees and the percetage that their number
> of sales represent in the total sales of the year. The rest of employees
> should be grouped as "Other", and Others must display the percentage of the
> total sales minus the TOP 5 ones..
>
> So my idea is to limit the Pie Chart to 6 slices, so the top 5 slices will
> show and the rest will be grouped, but I can't find how to to this.
>
> PLease, can someone help me achieve this? Thanks..
 | 
Pages: 1
Prev: Publishing a report to PDF
Next: Add the word "continued" in a Repeat Section