How does one add charts to a Report using the Chart Wizard

[rmoreno]

I am using Access 97 and have been using a query to generate a text report. The data from this text report is being used to fill out various Excel spreadsheets to create some charts. I am trying to bypass this step and generate the graphs from within MSAccess using various reports.


The problem I am having is using the Chart Wizard. I created my query to filter the data just the way I want. I select this query during the chart wizard selection process. I preview the chart and all looks well, however, whenever I click “FINISH”, the chart data gets summarized into quarterly results. Why is it doing this and how can I prevent this from happening?
More importantly, how do I change the underlying query of the chart to reflect the desired queried data results.

 

[davewhite04]

I'm messing about with this at the miniute also...

What I've found is that removing the GROUP BY and whatever aggregate functions that are in the select statement (avg,sum etc...) that is the row source of the Graph actually gives you what you want.

Hope this helps.

 

[Len Boorman]

Access Chart Wizard is a but of a bugger really. Advice is to keep it very simple to start with and get the chart up. Then see about "prettying" it up. Also you may find it easier to develop the chart on a form. To transfer the chart to a report jut select the chart and then do a copy and paste. It does actually copy the chart properties and data source correctly so if you are using parameters in teh query to select data they will also be supported in the report.

 

[rmoreno]

I am not sure I fully understand your response regarding the elimination of the GROUP BY statement. Could you look at the attached example database and demonstrate what you mean please?

 

[davewhite04]

The query that is the rowsource of the chart (qry_ChartingData) does not return the fields you have used in the select statement for the rowsource of the chart, therefore you are just getting default information.

I had to convert you DB so not sure if this will work, but try it...

Clear the row source of the chart and then add this:

SELECT fldDate,Belts FROM [qry_ChartingData];

 

[rmoreno]

Thank you so very much for pointing this out to me. Works as I had hoped it would. Could you help me to understand how to use the Chart Wizard and get the desired charting result. Being a novice to this I could use a bit of guideance on how to do this process correctly. Thanks

 

[davewhite04]

Well for simple charting that you have just done, then make sure that there is no aggregate functions(avg,sum,count etc.) used in the select statement of the row source of your chart, also make sure there is no group by. This seems to mess up the chart.

Also, if you are selecting data from a query like you are here (qry_ChartingData) run the query seperate to make sure it returns the desired results and make note of what the column names are in the query (fldDate and Belts in your case).

Make sure that your select statement in the row source of the chart includes the column names you saw when you ran the query(fldDate and Belts in your case).

Formatting the chart is a pain, right click on it and under chart object click edit, then right click on different areas of the chart and mess about to learn how it all works(you may need to create the chart on a form to get these options I think).

Also, create a form to manipulate you charts on as opposed to straight on a report, as someone recommended above, this offers much greater flexibility. You can then create a report and copy and paste your completed chart(s) onto the report and the report will automatically refresh the data.

Hope this helps.

 

[rmoreno]

Thank you for your inputs. I started a chart from scratch and inputted it into a form instead of a report. Things were much easier. I appreciate you explaining this to me in simple terms so that a novice such as myself could understand. Thank you again.

 

[davewhite04]

You're welcome, don't forget you can simply highlight your report on the form and right click, click copy and create a new report and simply paste your graph in the report.

Have fun.