Pie 1

[EasyOnly]

I have a chart that the the source code to it is the following:

SELECT [1Departmentsbyall].Department, Sum([1Departmentsbyall].SumOfAmountReimb) AS SumOfSumOfAmountReimb, [1Departmentsbyall].OrganizationID
FROM 1Departmentsbyall
GROUP BY [1Departmentsbyall].Department, [1Departmentsbyall].OrganizationID
HAVING (((Sum([1Departmentsbyall].SumOfAmountReimb))>2000));

I would like to specify to only pul the higest 8 department in the chart. right now, it may pull 20 or even more and the Pie does not look like a pie anymore....

 

[EasyOnly]

The above is linked to a chart in a report for each client and the goal is to pull the top 8 or 10 data for each client

 

[r937]

SELECT [red]TOP 8[/red]
       Department
     , Sum(SumOfAmountReimb) AS SumOfSumOfAmountReimb
     , OrganizationID
  FROM 1Departmentsbyall
GROUP 
    BY Department
     , OrganizationID
HAVING Sum(SumOfAmountReimb) > 2000
[red]ORDER
    BY Sum(SumOfAmountReimb) DESC[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[EasyOnly]

The above will work great if IU need the top 8 for all. but I would like to get the 8 top numbers for each client. same client may use the same departments.

Right now, I got the following:

Division Amount OrganizationID
A $4,074,022.00 2
B $1,168,062.00 2
C $733,326.00 2
D $384,993.00 31
E $192,250.00 109
F $182,915.07 2
G $175,516.00 56
H $154,166.67 57

But I want to find the top 8 division used by OrgID2, then top 8 for orgID#31...and so on.....

 

[dhookom]

I would save your first query as qtotReimb and then create another query like:

SELECT Department, OrganizationID, SumOfSumOfAmountReimb
FROM qtotReimb
WHERE Department in 
(SELECT TOP 8 Department
 FROM qtotReimb r
 WHERE qtotReimb.OrganizationID = r.OrganizationID ORDER BY SumofSumofAmountReimb DESC)

Duane
Hook'D on Access
MS Access MVP

 

[EasyOnly]

Wow.....Thank you so much for your help. You guys are the best!!

 

[MichaelRed]

A more common approach is to lump the remainder into an "Other" category. Showing the Top 'n' distorts the presentation and provides the viewer with the perception that the ones shown constitute 100% of the value. while they actually represent some fractional part.




MichaelRed