Solved

Pie / Pivot chart in Access report

Posted on 2011-09-02
15
385 Views
Last Modified: 2012-06-27
Hello everyone,

I would like to create a Pie chart on my report which is going to show the progress of my Projects.

My database contains numerous projects and each has let‘s say 5 stages which carry certain number of % out of the total 100%. Let’s say that for project 1 first stage is over: through a form I would enter that the first stage is done, meaning that for example 15% of the project is realized (I connected % and stage-IDs, so this works fine). After a while the second stage will also be done, so now additional 20% is finished. This means in total 35% of the project is done, and this is what I want my pie to show: a full circle with 2 cut parts, each for the two stages.

When I make a report I group the stages on project level. Now when I insert the Pie chart it shows each stage on a separate pie, which is meaningless for my report.

I don’t know how to make the Pie show together all the stages which refer to the same Project.

Can I do this through simply entering the pie chart on report, or do I need to create a Pivot chart, and how?
Can anyone give me some advice?

Thank you all.
0
Comment
Question by:DraganaRadic
  • 7
  • 6
  • 2
15 Comments
 
LVL 39

Expert Comment

by:als315
ID: 36473155
You can try to add 100 - sum of completed stages to some temporary tablle
Test example (open Form1, edit data, then press report button)
Db-CHART.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36473359
Please clarify.
Looking at your percentage figures, how are you sure that they will add up to 100%?
If there are 5 stages, @ 100%, this is 20% per stage.
In your example you listed stage1 as only 15%, then stage 2 as 20%...

Also a Pie chart can only really display all values adding to 100%.
It can sow a "partial percentage.

In other words, if you just have 15% and 20% The pie chart treats this as the whole, or 100%.
Again a pie chart cannot easily display a 20% slice and 15% slice if it does not know what the total will be.
Make sense?  

This is why a typical pie is based on "Values", and the pie chart itself calculates the percentages.

This is why you should always include a sample of your expected output.

JeffCoachman
0
 

Author Comment

by:DraganaRadic
ID: 36487653
als315>  Thank you for the comment,
I opened your sample DB, but it gives an error note when I press the EDIT DATA or REPORT button on Form1.

Can you give me a clue on how I can use your idea "add 100 - sum of completed stages to some temporary tablle
" ? As I see on your Pie chart, it summs the 2 entered stages, but is doesn't have this sum to 100 part. On the Pie, it only shows the 2 stages, not the 2 stages as a part of total 100.
Do I need an extra field in the Query for that?

Thanks

JeffCoachman> R you saying that if I change the percentages to regular numbers the Pie Chart is going to work as I want it to?  don't expect it to: even if I change the Formatting from persentage to Number, Pie is still gonna take my numbers as 100%. Hence the problem, how do I make the Pie look at the summ of my stages as part of the total 100.

I know my stages will summ to 100 when they all are entered , because for each I have assigned a specific percentage so that they sum to 100. They carry different percentage because they are not of the same importance and lenght.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 39

Expert Comment

by:als315
ID: 36487921
I think you have problems with my russian version of Access.
Try again.
I have now also new idea for you: you can combine all unfinished stages into one and show it on the chart
Db-CHART.mdb
0
 

Author Comment

by:DraganaRadic
ID: 36488160
als315> Thank you!

This is exactly what I wanted.
I just can't see where did you give the instruction for the "to do" part. I looked in all design views but I can't find it.
Can you explain to me how you introduced this "to do" part? Is in the chart itself, or in the query?

Sincerely,
Dragana
0
 

Author Comment

by:DraganaRadic
ID: 36488183
Ok, pardon me.
I forgot to look at the code on the REPORT button.
just dissreguard my last question!

I will try to input your chart into my database now, and hopefully you have solved my problem

Sincerely,
Dragana
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36489108
I see that als315 has you sorted, so I'll leave it to him...
0
 

Author Comment

by:DraganaRadic
ID: 36493859
als315>
Would you care to look at the sample database, it's very basic:

As I guessed, I didn't manage to connect the Pie with the ProjectID-s.

When you open the report rptProject you will see that the Pie shows all the stages, no matter to which project they belong. And I would need to make some kind of filter/restriction, so that the Pie is connected to the Project and it only shows those stages which belong to the Project in question.

Also, since now there is intermediate table (tblStage) I tried to modify the code for opening the rptProject from form frmProject, but I didn't do it well.

Thank you a lot.
Dragana
SampleDatabase.accdb
0
 
LVL 39

Expert Comment

by:als315
ID: 36494880
Your idea is not clear for me:
1. You've moved percents to tblStage (it seems to be reasonable only if Stage name will always be unique, may be better to have some table with ProjectID, StageID, Percent and Completed). In your example Stage1 will take 10% fo¿ all projects. Is it correct?
2. Table0 was proposed as temporary table only for chart, not as source for form (subform). In this case you can clear it and fill with query.
3. Do you like to have all projects in one report or you like to print report for selected project?
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 36494958
0
 

Author Comment

by:DraganaRadic
ID: 36495246

1. Stage1 will always take 10%; Stage2 always 20%, and so on. That's why I put percentages in that table, so the user can not insert the pecentages, they are always predefined. The user cna only chose the name.

2. I am very bad in using querys, and I intend to use something like Table0 in my real Database and use it to make a form/subform, like I did in the Sample Database.

3. I will have a list of all projects on one report which will contain all the data about projects and should also show the finished stages for each of the projects.

On the Sample base u attached, the REPORT button opens the project which is currently shown on the form, but it shows correctly the Stages.

But, in my real database, i open the reports from a separate form which lists all available reports. The user choses in a drop down list a report and opens it by clicking a OPEN button. If they chose a report with Project data, it will contain a list of all projcts with it's data (amongst other the finished stages).  
0
 
LVL 39

Expert Comment

by:als315
ID: 36495436
It is not so simple to combine in one report many charts and simple to make separate report for selected project.
Do you really need all projects in one report?
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 500 total points
ID: 36495532
This sample is based on your original idea, but with separate reports for every project.
SampleDatabase.accdb
0
 

Author Comment

by:DraganaRadic
ID: 36503137
The last database you attached works properly.

You gave me the baseline, and I will work from there and rethink my current report.

Now the report is a list of all the projects in one city, and I will omit the stages there. But I will make a report for each project separatly where I will include the Pie. In case I need it somewhere else, I will follow the logic you gave me here, and hopefully I ll manage.

I am so surprised that making the chart turned out to be so complicated, and I expected it to be the easy collorful part.

I will close this question now, and when I restructure the report and if I encounter a problem (which I most certainly will) I will open a new one.

Thank you very much for all the help!
Dragana
0
 
LVL 39

Expert Comment

by:als315
ID: 36508091
You are welcome
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question