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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
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
Hamed NasrRetired IT ProfessionalCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Hamed NasrRetired IT ProfessionalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Hamed NasrRetired IT ProfessionalCommented:
Welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.