Solved

Pie / Pivot chart in Access report

Posted on 2011-09-02
15
378 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now