Axis Titles in an Access Chart

I have a database of trainee evaluations of which the Options are Very Relevant, Relevant or Not Relevant. I am creating a chart in MS Access report and I would like to display all the options on the x axis even if the count is 0 . Currently if you look at the attached file course Id 1 does not have any trainee listing the course as relevant so it does not appear. Course ID 2 has all the values but only because the trainees have selected at least one of each.

Is there a way to make the axis show all possible options even if they are a null value?

Any help appreciated
DBCharts.accdb
tmckeatingAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
You did not do anything "worn"
The issue of "empty" values not being represented is a common issue.
;-)

But first, to be fair, there may very well be a more efficient way to do what you asked, so please review capricorn1's post as well...

What I did was to create a crosstab query as the source for the report.
This allowed me to put each "Relevance" in it's own column.
One feature of a Crosstab is that you can "Load" the columns with all possible values
See the last line in the Charts Recordsource:
    PIVOT Table1.Relevance In ("Not Relevant","Relevant","Very Relevant");
...with this line I am loading all of the "Relevant" (no pun intended) values in the columns

*However*, creating the chart from this data is the part that gets scary.
Create the query that displays the data in the way you need it.
Create a chart in the Report
Select the query as the source
Pull in all the fields you want
(*Where* you put the fields is something you have to play around with)
But if you drop them in the upper left, you will have to double-click each field and set it to "None", for the summary
There is no way around it, ...This is something you have to do quite a few times until you get it right...
;-)


<Also I now I have two charts for each course ID I guess I can just delete mine?>
Yep,(unless you want to keep it for sentimental reasons...)
;-)

<The charts in Access are not very intuitive.>
I know, trust me..., I know...
;-)

JeffCoachman
0
 
Rey Obrero (Capricorn1)Commented:
0
 
Jeffrey CoachmanMIS LiasonCommented:
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jeffrey CoachmanMIS LiasonCommented:
...and since you only have one series, you can delete the legend and add the same data to the chart Title
ScreenAccess-EEQ27990845ShowAllFieldsC.accdb
0
 
tmckeatingAuthor Commented:
Yes this is what I need but can you give me brief instructions on where I went wrong so that I would be able to replicate it again?  Also I now I have two charts for each course ID I guess I can just delete mine? The charts in Access are not very intuitive.

Regards

Tricia
0
 
tmckeatingAuthor Commented:
Thanks I did look at Capricorns but the teacher wanted the labels along the x-axis.

Many thanks from "Bonnie Scotland" :-)
0
 
Jeffrey CoachmanMIS LiasonCommented:
Se do bheatha
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.