MS Access Charting - How to Create Separate Charts based on Groups

[ShelDoyal]

Access 2000: I've created a report that is grouped on a specific field (Category Breakdown). I created a separate report using the Chart wizard to show trended data based on a query (past 30 days data for each category breakdown), then imported it into the first report as a sub-report. I did not get a prompt to link the reports based on parent/child fields (each report does have the field "Category Breakdown&quot. I want a separate chart to be created for each "Category Breakdown". In the subreport with the chart, I don't have an option for group, tried manually creating a group and moving the chart into the group header, but it made no difference. Tried moving the subreport into the main report group header, still no go. I'm not sure what else to try and I haven't been able to find anything pertinent in the Help files. I believe I managed to do something similar quite a while back, but can't remember how.

I'd appreciate any help you can give. Thanks.

 

[dhookom]

I'm not sure why you used a sub-report. It seems to me that you could place the chart on the main report and set the Link Master/Child properties to the Category Breakdown field. BTW: with Access 2000, you may need to add code to the report section containing the chart to requery it:

Dim obj as Object
Set obj = Me.MyGraph.Object
obj.Requery
Set obj = Nothing

Duane
MS Access MVP

 

[GingerR]

i agree with duane. make each page be a 'category': don't have a group header or anything. put the 'category breakdown' in the page header. OR if there is no related text or anything other than the chart, just put the chart on there, and the report doesnt even need a recordsource. just make the category appear in the chart title (not the report title).

 

[ShelDoyal]

The reason for the sub-report is each report has different criteria. The main report is based on a query which is based off two tables. First table contains number of tickets, date, and day of week for each category breakdown for previous day. The subreport contains number of tickets, daily average (# of tickets), and an Upper Control Limit (UCL = daily average + 3 standard deviations)for each category breakdown based on the previous four full months data. The main report shows the categories that had greater # tickets than the UCL and greater than 50 tickets. The subreport then provides trending data (previous 30 days) for each of those categories. On the printed report, I'd like to have a separate page based on Category Breakdown containing the previous days # tickets, the daily avg, and the UCL and below that a chart just showing the 30 previous days for the specific categories (for ease of viewing). We will send out the data to different groups for them to explain the call spikes. Not sure if this addition information helps, but if so, your reply is appreciated.

 

[dhookom]

OK, so you can't live without the subreport and the graph control is on the subreport. The graphed data is determined by two methods 1) the Row Source of the graph control 2) the Link Master/Child properties of the graph control. The link master/child works much like similar properties on a subreport control.

Does this help?

Duane
MS Access MVP

 

[ShelDoyal]

Well, I just had an epiphany...I created a new query combining all three tables. Orginally I thought I couldn't because the data would repeat 30 times for each of the category breakdowns due to the 30 days data in the 3rd table, which it did. However, I created a new report based on the new query and moved the fields from the original query used in the original main report from the detail section into the group header for Category Breakdown. I then created a chart using the fields from the original subreport query (now in the new query)and placed it into the group header. Now I have a separate page for each category breakdown with a chart showing only the data for that categoryl. All you who contributed--THANKS! You got me thinking about possibities which led to the solution.