Chart missing data

[BartSimpson]

Hi,

If I attempt to create a line graph based on a query that has a column or row that is all zero's then this column/row will be absent from the resulting graph. How do I force a graph to plot columns/rows that are all zeros?

BTW, this is not an issue with the 'new chart' method in Access 365, it's the old 'classic chart' (ActiveX control) method I can't seem to figure out.

Any advice would be appreciated.

Cheers,
John

 

[The_Doc_Man]

If the bottom axis of the chart is also zero, I think the axis overwrites the line because I think the axis is drawn 2nd in terms of when things actually appear on the chart. That all appears TO YOU at the same time, but it IS drawn in a particular order.

Can you try to make the trend-line wider, just to see if it "peeks over" the axis? And/or a different color?

 

[BartSimpson]

Thanks doc man.

By double clicking the graph I enter Microsoft Graph where I can select the table option to be able to view the numerical data beneath the graph. The column of all zeros (the last column in the data set) does not appear within the graph/table at all.

I should note that this is a filtered graph based on linked parent child relationships. If I create an unfiltered graph I don't experience the same missing data.

 

[JHB]

Could you post your database with some sample data, zip it because you haven't post 10 post yet?

 

[The_Doc_Man]

If you filter it, my very first question is to look at whether the FILTER is eating your graph by preventing data from being considered. If so, this isn't a problem in order of drawing components; it is a problem in suppressing components, which is a different thing entirely.

 

[BartSimpson]

See attached a database which outlines what I discussed earlier.

With this fictional example I need all dates from Feb to Sep to display on the x axis regardless if the value is zero. 'QueryTest' has missing data - the graph crosstab query in the row source fills it in (if that makes sense) but doesn't get displayed.

View attachment Database18.accdb

 

[isladogs]

Set the column headings for all the months 2/2018; 3/2018 etc in the crosstab query used for your chart. This will ensure all months see plotted including those with zero values

 

[The_Doc_Man]

Very good point, Colin. The Chart software is ... how shall I say it? Twitchy at times when it comes to zero values or MISSING values. I used to have all kinds of frustration drawing charts based on dates because the chart wizards cannot believe that you didn't want a CATEGORY axis as opposed to an x-y plot when time is one of the axes.

 

[BartSimpson]

Ok, I've set the column heading in the crosstab query for the date field. But in order to get the date field on the x axis I have to reorder the graph 'by row' within Microsoft Graph which gives me the same result as before.

What am I doing wrong?

 

[JHB]

Preparing the data for a graph can sometimes be a little tricky in MS-Access.
Attached is a solution, look at the 2 new queries qryPart1 and qryPart2 and the new report JHBReport.

 

[BartSimpson]

Thanks a lot JHB, that did the trick.

I was stubbornly trying to find a solution within the row source query of the graph and neglected to think of stand alone query solution.

I'd prefer to use the new graphing method within the 365 version but at the moment only one computer has it installed, the rest have the 2010 version which isn't compatible.

Just out of curiosity, is there a way to insert a data table within the new 365 graphing method similar to the old method. The new method has less options available - I don't see a data table option within chart settings. I could of course insert a data table as a separate object beneath the graph but that's additional work.

 

[JHB]

Thanks a lot JHB, that did the trick.

Good you've got it working.

Just out of curiosity, is there a way to insert a data table within the new 365 graphing method similar to the old method. The new method has less options available - I don't see a data table option within chart settings. I could of course insert a data table as a separate object beneath the graph but that's additional work.

I don't have the new 365, so I can't tell you.