Custom chart, add dummy row help

[skyline666]

Hi,

This thread can be seen as a follow on from an earlier thread i made, but that thread was how to make the chart in question (thread703-1442834).

I have two boston charts in Access 2003, with row source set to a query. What I want is to be able to have some sort of IF statement in the query, i'll explain what further on. If you take a look at the database included, you will see the two charts. The charts have their own query and table, and this is purely to show what I need, and don't normally have 2 tables/queries. When a user enters risk details, the user enters a risk rating between 1 and 9, and a manageability of either 1, 2 or 3. They enter these in a form, and so the fields RiskRatingY/O/R are automatically filled in with the correct values.

Just to explain the tables on the graph, Manageability is the Y axis, RiskRating is the side axis, RiskRatingY/O/R are used for the "background" and Risk is plotted on the graph.

The chart BostonSquare123 (the 123 being manageability of 1, 2, 3) shows exactly what I want (layout etc). I decided to copy the table and make all the risks a manageability of either 1 or 2 to see what would happen, and BostonSquare12 is the result, and not what I want. What I would like is if all the risks only have a manageability of 1 or 2, or 2 and 3 etc, then I still want the manageability of 3 for example to appear on the chart, just having no risks in. I have thought about maybe adding a row (through the query) to the end of the table to solve this problem, but don't know how. I would only want it to add a row if a manageability is missing (being either 1, 2 or 3).

Many thanks in advance,

Andrew

 

[MMSMSD]

This is just an observation and not a solution, unfortunately. I'm not sure exactly how you'd manage it but I think you're on the right track. In every instance, when I try to introduce IF statements in reports, I wind up giving up and working on it from the table or query level in order to get it working. If the query that the charts are based on do not show the data, the report won't show the data.

Adding a dummy entry would certainly show something on the chart but would artificially inflate the results. What might work would be to add a dummy entry for EVERY RISK LEVEL and then set the query to subtract 1 from the count. That would ensure that you always have something for each level and MAY show the level on the chart (even though the count would actually be 0 at that time. Haven't tried it but it's worth a shot. Hope this helps.

 

[dhookom]

I think you can do this without a chart control. There is a similar report display in the Miscellaneous Samples at

http://www.access.hookom.net/Samples.htm

Check out the Graph Plot Height/Weight.

Duane MS Access MVP

 

[skyline666]

Thanks Duane, i'll take a look at that tonight and over the weekend.