Access Pie Chart driving me crazy

kuznickic · #1 (**permalink**) January 11th, 2009, 09:51 AM

Hi all,

I am trying what I would think is pretty basic but I can't figure it out. I have one yes/no in a table that I want to display in a pie chart. I want to show all the yes and all the no's. I have created a query that counts the yesses and nos so that should populate the pie chart. I just can't figure out how to display this and like the title says it is driving me crazy. Any help would be greatly appreciated.

Thanks,
Chris

kuznickic · #2 (**permalink**) January 13th, 2009, 03:52 AM

Okay can somebody anybody please please help. I am getting desperate and have spent about a whole day researching and can't figure this out. I am begging anyone to help me.

mmcdonal · #3 (**permalink**) January 13th, 2009, 08:27 AM

Here is what I did.

I created a table with a Yes/No field, and populated it with sample data.

I then created a query that looks like this:

Query1:
SELECT Count(Table1.SampleID) AS CountOfSampleID, Table1.YesOrNo
FROM Table1
GROUP BY Table1.YesOrNo;

This grouped the Yes and No responses and gave me the following output in the query:

CountOfSampleID YesOrNo
5 Yes (checkbox)
3 No (checkbox)

I then created a report in Design View, and inserted a chart in the Detail Section:

Insert > Chart > Queries > Query1 > both fields > Pie Chart > Next > Name: Chart1

Then I open the report and the chart shows up as it should, with the legend -1, 0. This is because I didn't change the actual field to a Yes / No selection but kept the default -1/0.

What are you doing that is causing problems?

kuznickic · #4 (**permalink**) January 13th, 2009, 07:06 PM

Thanks mmc,

I'll give that a go. I think the problem rests more with my understanding of how pie charts work than Access. I know VBA but never really had to create any kind of charting capability before. Charts for whatever reason confuse me. I can create a query and view the chart in PivotChart view in the query but I just can't seem to make it work when I go to the report and try to create a chart in design view. The query I am using is this

Code:

SELECT Sum(Abs([Complete])) AS Completed, Sum(IIf([Complete],0,1)) AS NotComplete, Count("*") AS Total
FROM tblDiscrepancies;

. I think too that the chart designer in Access is not that intuitive.

Thanks,
Chris

kuznickic · #5 (**permalink**) January 13th, 2009, 07:31 PM

mmcdonal

THANK YOU, THANK YOU, THANK YOU!!!!

Works like a charm. I knew it couldn't be hard but for whatever reason that problem was kicking my okole.

Again many thanks.

kuznickic · #6 (**permalink**) January 13th, 2009, 07:46 PM

Okay just one quick question. How do I change the labels to say Complete and Not Complete.

Thanks
Chris

Old Pedant · #7 (**permalink**) January 14th, 2009, 01:48 AM

What do the labels say now???

I would *HOPE* that the chart is smart enough to pick up the names from your SQL query. Which means you SHOULD be able do do

Code:

SELECT Sum(Abs([Complete])) AS [Complete ], 
       Count(*) - Sum(Abs([Complete])) AS [Not Complete], 
       Count(*) AS [Total]
FROM tblDiscrepancies;

(Hope you didn't mind my simplifying your query slighly.) I put a space into [Complete ] so that the name would be the same as the name of a field. Sometimes the query engine chokes if you use a field name for an alias. It shouldn't, but it does.

Need to download code?

Navigation