Bar Chart with Straight Line Total Indicator

[floydpepper]

Ladies and Gents,
I currently hav a chart that tracks the names of classes completed per employee name. I have a chart report that displays this in bar chart format, with class names along the x-axis and the count of these classes (data indicating employees have taken the class) along the y-axis. Here is the underlying Row Source:

SELECT Null,Count([Tbl Main Training Schedule].Portal) AS Portal, Count([Tbl Main Training Schedule].[Architecture 101]) AS [Architecture 101], Count([Tbl Main Training Schedule].JCIDS) AS JCIDS, Count([Tbl Main Training Schedule].POPKIN) AS POPKIN, Count([Tbl Main Training Schedule].[AARMS/CADIE]) AS [AARMS/CADIE], Count([Tbl Main Training Schedule].BONAPART) AS BONAPART, Count([Tbl Main Training Schedule].UML) AS UML, Count([Tbl Main Training Schedule].[AFIT SYS 183 (On-Line)]) AS [AFIT SYS 183 (On-Line)], Count([Tbl Main Training Schedule].[AFIT SYS 283]) AS [AFIT SYS 283], Count([Tbl Main Training Schedule].[AFIT SENG 540-NC]) AS [AFIT SENG 540-NC], Count([Tbl Main Training Schedule].[AFECA 503 Short]) AS [AFECA 503 Short], Count([Tbl Main Training Schedule].[AFCEA 503]) AS [AFCEA 503], Count([Tbl Main Training Schedule].[AFCEA 504]) AS [AFCEA 504], Count([Tbl Main Training Schedule].[AFCEA 505]) AS [AFCEA 505], Count([Tbl Main Training Schedule].[ODU/VMASC]) AS [ODU/VMASC], Count([Tbl Main Training Schedule].[Rational (Technical)]) AS [Rational (Technical)], Count([Tbl Main Training Schedule].MCSD) AS MCSD FROM [Tbl Main Training Schedule];

I want to have the total number of employees (97 currently, but subject to change) to be calculated and displayed as a line across the bar chart for a quick visual reference (to show how many of the total have taken the class in question).

Can anyone help me with this?

Thank you in advance.

FloydPepper

 

[GingerR]

Hi. The first thing is to get a total column into your query. it will be the same value (i.e. 97) in every row.

Then, if your chart 'template' is showing that North-South-East-West thing, let's change that so it's easier to format the chart: run your query; hit ctrl+A (select all); close the query; double-click on the chart; view the Datasheet; select the upper-left cell (so the whole thing is selected) and hit ctrl+v (paste) (answer YES/OK to the pop-up that says the data isn't in the same format). This pastes in your data as the "template data" so you can better see how the chart will really look. Don't worry: the data will still change in the print preview of the chart. This is just the "template data".

Ok, so now you have this column for TotalStudents, for every X value. In the chart design, pick that piece of the column/bar, and from the menu select CHART - Chart Type. Make it a line. Format it as you wish.

So try that out and let us know how it goes.



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

http://r937.com/relational.html

 

[floydpepper]

Thanks GingerR.

I added the Total column to my query by inserting Count([Tbl Main Training Schedule].Last Name) AS Last Name. This pulls up a final column in the report that towers over the others. My problem is that this column is still grouped with the other totals (for each class/column 1). How can I designate this value for each of the others as a separate column? I'm a novice with just enough knowledge to get myself in trouble. Thank you again.

FloydPepper

 

[GingerR]

Did you continue on with the instructions I gave you? PICK that piece of the column with your cursor, and choose CHART+Chart Types from the menu. THen make it a LINE.

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

http://r937.com/relational.html