How to add a pie chart to my Access report

Question

I have a table that shows "DONE" and "REMAIN" for each "AREA" like below:

 AREA       DONE    REMAIN TOTAL
 AREA1      100     200     300
 AREA2      200     300     500
 AREA3      200     700     900

Now I like to make a report that shows "DONE" and "REMAIN" in each AREA with pie chart, now I have problem how I have to do this job for the graph, how should be "row source" of chart control. What query needs on this table?

Answer 1

(I'll assume that your table is named [AREAS].)

The trick here is to create a saved UNION query named [AreaDataForPieChart]...

SELECT "DONE" AS PieCategory, [DONE] AS PieValue, [AREA] FROM [AREAS]
UNION ALL
SELECT "REMAIN" AS PieCategory, [REMAIN] AS PieValue, [AREA] FROM [AREAS]

...returning...

PieCategory  PieValue  AREA 
-----------  --------  -----
DONE              100  AREA1
DONE              200  AREA2
DONE              200  AREA3
REMAIN            200  AREA1
REMAIN            300  AREA2
REMAIN            700  AREA3

...and base the pie chart on that.

Start by creating a Report based on the [AREAS] table, and display [AREA] in a text box:

Now add a Chart control to the Detail band of the report. When prompted, choose the saved query we created above

Choose [PieCategory] and [PieValue] as the columns for the chart

Choose "Pie Chart" as the chart type

The default data layout will work fine because of the order of the columns in our query

Tell the report to link the chart to the main report by using the [AREA] fields

Give the chart a meaningful title, then click the "Finish" button.

Now preview the report. It should look something like this: