Combining count values for a pie chart

[wlkr.jk]

Hi,



I'd like to combine a few values to compare with another and then present in a pie chart. How am I supposed to formulate this in a query.

I've got a main table listing out a vast amount of values with one field being StatusID.

There are 5 possible statusID's:
1,2,3,4,5.

In the pie chart, I'd like to sum the count of the number of times StatusID's 1,2,3, or 4 show up in the main table (i.e. combine all the individual sums). The other count will be of StatusID 5. So, therefore I will have 2 counts to display in the pie chart.

Thanks for your help.

[June7]

Try:

SELECT Count(IIf(StatusID<5,StatusID,Null) AS CountOthers, Count(IIf(StatusID=5,StatusID,Null) AS Count5 FROM tablename;

[wlkr.jk]

Unfortunately that didn't completely work. It assigned a common name for all statusID which met the criteria "<5" but it didn't actually combine them in the chart. (See attached picture)

Also, the criteria where StatusID=5 didn't work because it didn't display in the chart that count. I had 1 record which had StatusID=5 but it didn't count obviously.

Here is what I ended up using:

SELECT ProblemTable.StatusID, Count(IIf(StatusID<5,StatusID,Null)) AS CountOthers, Count(IIf(StatusID=5,StatusID,Null)) AS Count5
FROM ProblemTable
GROUP BY ProblemTable.StatusID;

[June7]

I have your db from the other thread.

The query in the RowSource is not what you have posted.

This works and is in line with the query you posted:

SELECT IIf([StatusID]<5,"Open","Closed") AS Status, Count(IIf([StatusID]<5,"Open","Closed")) AS CountStatus FROM ProblemTable GROUP BY IIf([StatusID]<5,"Open","Closed");

Change the chart Data Labels settings to not show the series name - display the category name instead. Or show the legend.

[wlkr.jk]

Thanks June7. So I settled on the following:

SELECT IIf([StatusID]<5,"Open","Closed") AS Status,
Count(IIf([StatusID]<5,"Open","Closed")) AS CountStatus
FROM ProblemTable
GROUP BY IIf([StatusID]<5,"Open","Closed");

By doing this, I was able to group everything into two categories on the pie chart (Open and closed).