Grouping w/ Graphs in Rpts

[RJF]

I’ll explain the problem as best as I can.
I have a report based on a query. The query contains the following data:

Company_Name (Demo, Inc.)
MonthDate (01/2008)
ItemDescr (Copy and Printing)
Expected (125,000)
Actual (120,000)

The report is grouped by Company_Name, then ItemDescr, then sorted by
MonthDate. It runs great. Looks like this.

---------
Demo, Inc.

Copy and Printing

Month Expected Actual
01/2008 125,000 120,000
02/2008 125,000 127,000
---------

Now the problem… I put a graph in the same report using the wizard. It
also needs to be grouped by Company_Name and ItemDescr.

I placed the graph in the ItemDescr header. I tried to link the
Company_Name and the ItemDescr fields on the report with the graph but then
the graph appears blank. If I only link the ItemDescr field, the data
appears but it adds all the data from each Company_Name together on the same
graph. (Which is fine if I was only running it for one company.)

The statement behind the graph is:

SELECT MonthDate, Expected, Actual FROM qry_Results GROUP BY MonthDate;

How do I get the graph to be grouped by Company_Name AND ItemDescr?

Any help would be greatly appreciated.

Thank you,

 

[RJF]

Oh yeah, I'm using Access 2000.

 

[Duane Hookom]

Do you have both of the Link Master fields in the ItemDescr group header?

 

[RJF]

Hi Duane, thanks for your help.

I tried it with the Company_Name and the ItemDescr in the ItemDescr group
header, that is where the graph is located as well. The graph shows up blank
if I have Company_Name;ItemDescr in the Child and Master links.

I'm sure I'm missing something pretty obvious and I know it can be done.
I'm not very experienced with graphs.

I'd appreciate any suggestions.

Thank you,

 

[Duane Hookom]

I'm not sure what you problem might be. I have a sample of how to link with
two fields at http://www.access.hookom.net/Samples.htm.

You may need to add code to Requery the chart control in the On Format or
Print event of the section containing the chart control.

 

[Duane Hookom]

You could compact and zip it then email it to me (no charge) at duane (AT)
hookom (DOT) net. I should have some free time over the next couple holidates.

 

[Duane Hookom]

The solution involved:
- renaming the field named "name" to anything other than "name"
"name" is never a good name of any object in Access since
every object has a name property
- having "both of the Link Master fields in the ItemDescr group header"

 

[RJF]

Duane,

I changed the field called Name to Company_Name.

I copied the Company_Name field into the ItemDescr group header. Now it
contains both the Company_Name field and the ItemDescr field. (I tried it
with the Company_Name field still in the the Company_Name header and then
without it in the the Company_Name field header.)

I put Company_Name;ItemDescr in Link Child and the Link Master in the graph
properties.

When I run the report, the graphs are blank.

It seems so easy as you explain it. I can't figure out what I can be doing
wrong.

Any ideas?

 

[Duane Hookom]

It worked as expected for me. Did you change the Row Souce of the chart
control? A chart/graph control is much like a subreport. The subreport has a
Record Source while the chart has a Row Source. Both use the Link
Master/Child to filter the records in the sub/chart. The fields used in the
Link properties don't have to be displayed in the sub/chart. You should bind
a control in the main report's section containing the sub/chart to all link
fields.

 

[Duane Hookom]

I sent you a copy of my working results.

 

[RJF]

It's working! Thank you so much. You have gone above and beyond what I ever
expected.

YOU ARE THE BEST!