How can I Create a pie chart from calculated data?

[irethedo]

I have a report that calculates percentages of instances of a certain number and I wish to create a pie chart to display this data but when trying to use the wizard, it appears that I can only chart table & query information.

How can I create a pie chart which contains 2 or 3 pieces of calculated data?

Thanks

Irethedo

 

[OnTheFly]

I would think that any caculations you are doing in a report could also be done in a query. That is the route I would choose.


Hope this helps.

OnTheFly

 

[irethedo]

Ok...
I am new to this so here goes...
I have a form in which I input start and end dates and then have a button to either preview or print one of a few reports. In one of the reports that I launch, I run a query that gives records within a certain date range from the date data from the form.

These records are results of tests that either pass or fail and two of the fields within my report consists of formulas to figure out the percentage of records that passed and a percentage of records that failed, and this is what I want to chart in a pie chart.

I understand what you are saying about calculating this within the query but, is it possible to create this calculation within the query itself so that the pass/fail data for all records within a range is stored in a field of the same table for each record?

How can this be accomplished?
Please cite an example or two...

Also, can the colors of the segments of a pie chart be defined by me?

Thank you!

 

[OnTheFly]

OK, here is an example

My table is called tblTestResults

The fields are ID(AutoNumber), TestDate(DateTime), and TestPass(Yes/No)

The query attached to the record source of the Chart would be as follows.

SELECT IIf([TestPass]=True,'Pass','Fail') AS PassFail, Count(ID) AS TotalTests FROM tblTestResults WHERE TestDate Between #1/1/2005# And #1/31/2005# GROUP BY TestPass

With a pie chart you don't have to figure the percentages, it does it for you.

Yes you can modify the chart. If you double-click on the chart object it puts you into edit mode. Then double-click on the pieces of pie to edit their properties.

Hope this helps.

OnTheFly

 

[irethedo]

Thanks ONTheFly...

I tried your example but I did not know how or where to enter the query information that you suggested:

SELECT IIf([TestPass]=True,'Pass','Fail') AS PassFail, Count(ID) AS TotalTests FROM tblTestResults WHERE TestDate Between #1/1/2005# And #1/31/2005# GROUP BY TestPass


So unfortunately I could not get it to work...

Do I enter this formula into the criteria of the query?
(I have access 2000 and I am not sure where this goes...)

Also, is it possible to have a query sort through records within a certain date range and calculate the yield rates of the combination of all records and store this information in a field of the table for each record? How is this best done?

Thanks

Irethedo

 

[OnTheFly]

The query would be entered into the Chart Objects Record Source property.

If you select the chart object in your report and then bring up the properties window you will see the Record Source property.

As for the second question, I am not sure what you are asking.

Hope this helps.

OnTheFly

 

[irethedo]

Ok.. thanks that worked!

I am now trying to apply this to my database and struggling at it too...

I have a query called GSI FPY Query

The fields in this query are from my table
with the shown types as follows:

Date - (date/time)
Operation - (text)
Fail Quantity (number)
Pass Quantity (number)
Assembly Number (test)

I have the following criteria in my query:

Date = Between [forms]![print or view reports]![StartDate] And [forms]![print or view reports]![EndDate]

Assembly Number = [Assembly Part Number]

===
From a form I enter the beginning and end dates and the query prompts me for the assembly number.

In my report I calculate a pass yield rate and a fail yiid rate which I wish to chart with a pie chart.

The calculations are as follows:

Pass Rate = Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))

Fail Rate =Sum([Fail Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))


I am trying to figure out how to combine all of these different criterias to fill the pie chart with the pass rate and the fail rate...

Any suggestions?

Thanks much for your help!