Leaving Out Null Values in a Chart

[Dave177]

I am having a problem leaving out Null values in a bar chart.
Basically, I have a chart showing average scores for tutors, grouped by a course date. There are a total of 12 tutors but only 4 teach at any given course - so the remainder receive no score whatsoever for most courses. My chart at the moment has spaces between the plotted series for those tutors that are not teaching. I am trying to get rid of these spaces.
The data for the chart comes from a query which uses the average aggregate function for course elements such as [SubjectA] and [SubjectB] and an expression [Average Score Received] totals these up and divides by the number of course elements. This [Average Score Received] is grouped by [start_date] of course meaning that there is an average score received for each tutor for each course that they teach on. Why are there and how can I get rid of the spaces left for tutors who did not teach on a particular course.
I have tried in Graph going to Tools-Options-Chart but there is no option of Not leaving gaps.
Any help would be very much appreciated!

Dave

 

[M8KWR]

in the recordsource in the chart you want to include "is not null" in the crieria of the field you do not want to display blank records

If you are unsure where to put this paste your sql code from the record source of the chart.

Hope this helps

 

[Dave177]

M8KWR,
Thanks for your reply.

I tried putting IS NOT NULL at the end as follows:

TRANSFORM Avg([Average Score Received]) AS [AvgOfAverage Score Received] SELECT [Course_instance] FROM [qryCourseAssessmentsTutorAverages] GROUP BY [Course_instance] PIVOT [Group_tutor] IS NOT NULL

but then it grouped all the series together instead of under the name of individual tutors. It did however get rid of Null values!
Any more help would be greatly appreciated!

Dave

 

[GingerR]

I don't think it's Group_Tutor that you want to put Is Not Null is it? Isn't Group_tutor a person's name? I think you want to put Is Not Null or <>0 in whatever number field which, when it comes up as zero or blank, you don't want shown.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dave177]

Ginger,
Thanks for your help on this one!
I opened up the SQL Statement Query Builder for the chart. The three fields were as follows:
[course_instance] was given the 'Row Heading' property for the crosstab row.
[Group_tutor] was given the 'Column Heading' property for the crosstab row.
[AverageScoreReceived] was given the 'Value' property for the crosstab row.
In the criteria row for [AverageScoreReceived] I tried to put "Is Not Null" but an error said that I could not specify criteria on the same field for which there was 'Value' entered in the crosstab row. So I inserted another [AverageScoreReceived] field into the grid and in the Totals row I put 'where' and in the criteria row "Is Not Null" however the chart still showed the blanks for those tutors not attending.
What am I doing wrong?

Dave

 

[GingerR]

please post some sample data of the result of the crosstab query

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dave177]

Ginger,
The result of the crosstab query for the chart is as follows.

There are 9 Columns: the 1st being [course_instance] and then the 8 listed tutors.
Data in 1st Row: [course_instance] is 11/11/2004 and the only tutor with data is Doug with 0.1. All the other columns are blank
Data in 2nd Row: [course_instance] is 13/05/2004 and the only tutor with data is Anne with 2.4 - other columns blank
Data in 3rd Row: [course_instance] is 13/11/2004 Bryan and Hilary both have 1.3 - other columns blank
Data in 4th Row: [course_instance] is 15/12/2004. Julian has 4. - other columns blank
Data in 5th Row: [course_instance] is 28/09/2004. Bryan has 1.6 and Chris 2.5 - other columns blank
Data in 6th Row: [course_instance] is 30/11/2004. Garry has 3.5, Jill has 2.5. - other columns blank

These blank spaces are still shown in the chart. There should be more data in each row of the chart (i.e 4 different tutors) but I am just experimenting at the moment.

Regards
Dave.
P.S I hope this is what you meant by posting results. D

 

[GingerR]

I guess I don't see what's wrong with this? Each of the tutors you listed have data, so they should be shown on the chart, right?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dave177]

Yes but for each [course_instance] in the chart on the x-axis there are spaces between each tutor. For example on in the 3rd Row the value columns for Bryan and Chris are next to each other with big gaps either side (presumably for the other tutors with Null values). On the 5th row the columns for Bryan and Hilary are further apart. These spaces reflect my tutors list (aphabetically) i.e Bryan and Chris are next to each other in my query, while Bryan and Hilary are further apart. It's these spaces I am trying to get rid of.

D

 

[GingerR]

Sorry but I'm having a hard time visualizing this. If you want me to look at it, post your email address and I'll write you with mine and you can zip and send it to me if you want. I have a lot of chart experience but can't "see" this one.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dave177]

Thanks!!
its david_hawes@yahoo.com

 

[Dave177]

Sorry! that was david(underscore)hawes@yahoo.com

as in david_hawes@yahoo.com

 

[GingerR]

Dave--I looked at your chart, and your 'blanks' are not going to go away. You have a column chart, with the columns next to each other, for each test. If you add a data table to the bottom, you'll see the data aligning with each 'test'. Since some tutors have no data for a particular test, there will be no column for them. That's just the way it is. Honestly I don't really see anything wrong with that. Is there an issue?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dave177]

Ginger,
You're right there's nothing really wrong with it except that it means that the data on the x-axis is more squashed up without showing as much data (due to the many blank columns). Anyway, thanks for all of your help on this one!


Dave

 

[GingerR]

Hi Dave--You could make your chart landscape so it's wider. If you make .5" margins, you can make the chart 7.5" wide. And change your fonts so they are smaller (10 pt). Maybe those things will help a little bit.

Have fun!

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244