How do I creat Pie Chart in a MS Access 2010 Form?

I am trying to place a pie graph of Market Values on a form in my database.  This should be easy, but I just cannot get it to work.

Basic Information:
The database is used to report and analyze clients' investment holdings.  The data is supplied by flat file exports from commercial software.  That basic data is  simply each "tax lot" of each holding in each client account.  The database sums that information, grouping on various characteristics of the holdings, so we are able to analyze and report on these holdings as needed.

Axys_Link_Summary_Table is the "master table" generated, daily, by a MakeTable query supported by numerous queries alluded to above.  The database runs smoother by having these comprehensive queries generate this table for further use, rather than running compounded queries, frequently .

After using the Chart Wizard to place a simple pie chart on a form that displays summary information about an account, I have this in Row Source:

SELECT  Axys_Link_Summary_Table.MV_Fx_Tot, Axys_Link_Summary_Table.MV_EQ_Tot, Axys_Link_Summary_Table.MV_Cash1 FROM Axys_Link_Summary_Table;

Column Count = 3

This is supposed to create a very basic chart showing "Stocks", "Bonds" and "Cash" as proportionate slices of their totals.
It doesn't. I get no slices, just a filled pie with a single radius line and a legend with the value of MV_Fx_Tot displayed (the total value of the "bonds" in the account).

To be clear, there is no need for Sum() since the table is data that has already been summed. I tried that, too.

I have tried all sorts of other approaches like creating a subform - get same result in that form, building a separate query from  Axys_Link_Summary_Table - no luck.

What am I doing wrong??!!

Thanks in advance...
Brince61Asked:
Who is Participating?
 
hnasrCommented:
Pie chart was confusing, although it looks straight forward.

Try this:
What?
 Insert column chart, then change to pie.
How?
1 Insert chart control - follow wizard
   select query - next
   select all fields - next
   select column chart - next
2 Data box contains SumOfCash - series box empty - Axis Box contains AcctNumber
3 Drag AcctNumber from Axis Box to Series Box
4 Drag Stock to Data Box (added as SumOfStock)
5 Drag Bonds to Data Box (added as SumOf Bonds)
6 Double click SumOfCash in data box, and select None (Shows Cash) - next
   repeat with:
   SumOfStock (Shows Stock), and
   SumOfBonds (Shows Bonds)
   next - next - finish
At this point, you may modify the chart object, or a duplicate, to pie chart type.
7 Right click chart object - select Change Object - Edit
8 Select chart - change Type - Pie - 3D
0
 
hnasrCommented:
Upload a sample database including table with few simple records.

Show an image of the required chart.
Is it a summary chart or one for each record?
0
 
Brince61Author Commented:
Here is a DB with a table using some of the fields from the actual table and an image of the basic pie chart I would like to generate.  Once I am able to do this basic version, I can expand it, as needed.

For this situation, the chart is for each record.  The table is populated with sums of all the positions in the client's accounts by simple category (Cash, Bonds, Stocks).  The table also has sums that break out those categories into directly owned securities and mutual funds under those categories.

For now, I am only looking to chart the basic Cash, Bonds, Stocks groups which are [MV_Cash1], [MV_Fx_Tot      ] and [MV_EQ_Tot], respectively.

The actual field names come from the following:
MV is Market Value
Fx is fixed income (Bonds)
EQ is equity (Stocks)
Tot is Total
Image of how chart should appear - changing as user advances to each record in formSample-DB.accdb
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
hnasrCommented:
Check this database, and comment, to clarify my understanding.

For a pie chart, you need one line of data. The values for it represent one data point in a series.
Sample-DB-2.accdb
0
 
Brince61Author Commented:
Thank you for the reply.  I am still stuck, though!

Below is a step-by-step review of my struggle to accomplish this basic task.  This is as embarrassing as it is frustrating!  I know this is simple, but I am not getting the right results.

I have arranged my objects just as you have in your Sample-DB-2.accdb .

I considered that Null values in some records might be a problem, so I tried a modified query using NewFieldName:IIF([Field] Is Null,0,[Field]) to place a 0 instead of a blank in the query results, but that did not make a difference.

So, what follows is, as well as I can tell, exactly as your example in Sample-DB-2, without any adjustments for Null values.

I used the “Chart Wizard” and chronicled those results  with screenshots.  Then I modified the design the Wizard made to duplicate your example.  Neither works.

This is the SQL from “qry_Allocation_Chart_2”.  This is equivalent to “Query1” in your example.

SELECT Axys_Link_Summary_Table.AcctNumber, Sum(Axys_Link_Summary_Table.[MV_Cash1]) AS Cash, Sum(Axys_Link_Summary_Table.MV_EQ_Tot) AS Stock, Sum(Axys_Link_Summary_Table.MV_Fx_Tot) AS Bonds
FROM Axys_Link_Summary_Table
GROUP BY Axys_Link_Summary_Table.AcctNumber;

Please refer to the attached Images.  They are screen shots of the steps I took to get the design results and the output results.

Chart Wizard Step 1 v1 – shows fields selected from query (qry_Allocation_Chart_2)
Chart Wizard Step 2 v1 – shows 3D pie chart selection
Chart Wizard Step 3 v1 – shows fields in the chart (this is confusing, since it only allows the user to drag one data field to the chart)
Chart Wizard Step 4 v1 – shows the linking fields
Chart Wizard Step 5 v1 – shows legend option

Chart Wizard Design Result v1 – shows the design results of the Wizard
Chart Wizard Result v1 – shows the form’s “Form View” using Wizard’s design

Form Redesign v2 – shows the adjustments made to Row Source to mirror your example
Form Redesign Result v2 – shows the form’s “Form View” after Row Source adjustments
Chart-Wizard-Step-1-v1.png
Chart-Wizard-Step-2-v1.png
Chart-Wizard-Step-3-v1.png
Chart-Wizard-Step-4-v1.png
Chart-Wizard-Step-5-v1.png
Chart-Wizard-Design-Result-v1.png
Chart-Wizard-Result-v1.png
Form-Redesign-v2.png
Form-Redesign-Result-v2.png
0
 
Brince61Author Commented:
Solved.
Thank you hnasr!  
I am eternally grateful and can move now move on in life!
0
 
Brince61Author Commented:
hnsar stayed with my problem and provided me a simple "workaround" for what appears to be a MS Access glitch.
0
 
hnasrCommented:
Welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.