Add Conditional Formatting to a Chart

[June7]

UNION does not allow duplicate records, UNION ALL will. The 'D' and 'X' calculate a value that identifies source of the record, hence the field name Source. This assures the 3 'dummy' records are not duplicates of any real records.



Did you mean to say 'it isn't setting "low" to 1' - your narrative said 0.

No idea why the other charts don't work.

[ItsRoland]

Yes, I meant 1, not 0, sorry.

And okay, I'll keep messing with it. Hopefully I manage to get them working.

Thanks for all your help

[ItsRoland]

Okay, so this is really odd. I haven't changed anything, but when I opened up the report this morning, the Pie Chart is displaying incorrect percentages again (same as before, 33% for "High", "Medium", and "Low").

Access Charts are going to be the death of me.

[June7]

Provide db for analysis. Follow instructions at bottom of my post.

[ItsRoland]

Cyber Risk Database - Working - Chart Troubleshooting.zip

The report is called "rPieChartTest"

The chart titled "Overall Risk" is tied to "qOverallRisk" -- This pulls from the field "riskRating" in the table "tScenarios"
The chart titled "Overall Residual Risk" is tied to "qOverallResidual" -- This pulls from the field "residualRisk" in the table "tScenarios"
and the chart titled "Risk Without Key Security Measures" is tied to "qNcmRisk" -- This pulls from the field "ncmRisk" in the table "tScenarios"


Thanks again for the help

[June7]

Since you are charting a count and the count is 1 for each category, the percentages are correct.

Since UNION query does not allow duplicates, will need to include scenarioID field. I should have noticed that before, sorry.

SELECT scenarioID, tScenarios.riskRating, IIf([riskRating]="Low",1,IIf([riskRating]="Medium",2,IIf([riskRating]="High",3))) AS Expr1, IIf([Expr1]=1,"Low",IIf([Expr1]=2,"Medium",IIf([Expr1]=3,"High"))) AS Expr2
FROM tScenarios
UNION SELECT 0, "Low", 1, "Low" FROM tScenarios
UNION SELECT 0, "Medium", 2, "Medium" FROM tScenarios
UNION SELECT 0, "High", 3, "High" FROM tScenarios;

[ItsRoland]

I applied the changes to "qOverallRisk", but nothing changed on the chart. Am I missing something?

Is there a more efficient way of displaying the number of "High", "Medium", and "Low" values in a column as a percentage?

[June7]

I did the suggested edits and definitely changed chart output. I also modified data so a category did not have any Low values. All works perfectly.

[ItsRoland]

I'm not sure how you got anything to change on the chart. Did you change anything in the RowSource property?

I copy/pasted the code you posted into "qOverallRisk" and am not seeing any changes on the chart. I even removed all "Low" values in "tScenarios" and the chart is still displaying "Low" as 30%. The datasheet view of the query is also showing scenarioID = 0 72 times with High, Medium, and Low 24 times each, which I imagine is what's causing my issue.

[June7]

Only design changes were to the UNION queries. Turns out the calculated Source field is not needed but doesn't hurt to have.

Oops, I copy/pasted the wrong SQL from earlier post. Remove ALL from the query. Review edited earlier post again. At least you now see what using ALL does.

[ItsRoland]

Both Print and Report view are still not showing correct data. There is not a single instance of "Low" anywhere, yet "Low" is still listed at 30%.

Like I said, I think the issue is coming from the 72 records appearing on the query that shouldn't be there. 24 are "Low", 24 are "Medium", and 24 are "High", all of which have scenarioID = 0. Is this not occurring on your end?

[June7]

I must have edited post after you read it. Review again.

I am doing these posts via phone not computer (internet connection issues) which is why I did not copy/paste sql from db. Sorry for confusion.

[ItsRoland]

Awesome, everything appears to be working now, thank you! Hopefully I don't come in tomorrow and everything is suddenly broken again.

I really appreciate your help, I doubt I would have gotten these charts to work on my own.