Multiple charts in a report

J

Jan van Hooff

Hello,
I have made a query with 3 fields: linenumber, weeknumber and quantity.
When I make a chart in a form (with the wizard), I get for every line a chart.
In this chart the quantity is visible for each week.
When I try to make a chart in a report, I get all lines in 1 chart.
How can I get a chart for each line in the report, without making different
queries?

Jan
 
D

Duane Hookom

Make sure your report's recordsource contains 1 record for each line. Then
use the Link Master/Child properties of the graph control to limit the lines
to just the current line.
 
J

Jan van Hooff

Hello Duane,
Thank you, but I am afraid I didn't understand it quit well. In my query for
every week there is a record for each line. In the chart I want to see the
quantity per week on each line. I have 9 lines. So I expect 9 charts, but I
get 1 chart, with 9 lines.
When I set in the master/child properties "line", and I view the report I am
asked to fill in the parameter. When I fill in the line number then I see the
chart with 1 line.
But, if it is possible, I want to see 9 charts at once.

Jan
 
D

Duane Hookom

Does your report's record source contain 9 records (1 for each line) as
noted in the first line of my previous reply? If it did, you should not be
prompted for the line.
 
J

Jan van Hooff

Sorry Duane,
It did not.
I have 9 records for each week, for example:

week number line number quantity
1 1 1000
1 2 800
.. . .
1 9 2000

and so on for each week.
So I want to see a chart with on the x-axis the weeknumber and on the y-axis
the quantity. And this in 9 seperate charts.

Jan
 
D

Duane Hookom

When I suggest your report's record source should have 9 records (one for
each line) I would expect that you would try this.
 
J

Jan van Hooff

Hello Duane,
Sorry for my bad English. ( I am Dutch).
This is what I tested:
I changed the design of my query: In the field "week" I filled in as
criteria number 0440.
This means that I got 9 records in the query: for each line the quantity of
week 0440.

Afterwards I made a chart with the reportwizard.
In the properties of the chart I filled in "line" in the master/child
properties.

This was my recordsource: TRANSFORM Sum([quantity]) AS [sumofquantity]
SELECT [Week] FROM [q ffcnc pr wk pr ln] GROUP BY [Week] PIVOT [Line];

So the chart is exactly the same as before (all 9 lines in 1 chart). Only on
the x-axis there is only 1 point: week 0440.

Jan
 
D

Duane Hookom

Consider changing the Row Source of the chart to:

TRANSFORM Sum([quantity]) AS [sumofquantity]
SELECT [Line] FROM [q ffcnc pr wk pr ln] GROUP BY [Line] PIVOT [Week];


--
Duane Hookom
MS Access MVP
--

Jan van Hooff said:
Hello Duane,
Sorry for my bad English. ( I am Dutch).
This is what I tested:
I changed the design of my query: In the field "week" I filled in as
criteria number 0440.
This means that I got 9 records in the query: for each line the quantity
of
week 0440.

Afterwards I made a chart with the reportwizard.
In the properties of the chart I filled in "line" in the master/child
properties.

This was my recordsource: TRANSFORM Sum([quantity]) AS [sumofquantity]
SELECT [Week] FROM [q ffcnc pr wk pr ln] GROUP BY [Week] PIVOT [Line];

So the chart is exactly the same as before (all 9 lines in 1 chart). Only
on
the x-axis there is only 1 point: week 0440.

Jan





Duane Hookom said:
When I suggest your report's record source should have 9 records (one for
each line) I would expect that you would try this.
Click to expand...
Click to expand...
 
J

Jan van Hooff

When I do so, only the x-axis and series are changed: On the x-axis is
standing the line number. The series is Week. Still 1 chart.

I don't know what I am doing wrong. In the meanwhile I have made 9 separate
charts in 1 reports. This is oke for me.

Anyhow thanks for you advise.

Jan

Duane Hookom said:
Consider changing the Row Source of the chart to:

TRANSFORM Sum([quantity]) AS [sumofquantity]
SELECT [Line] FROM [q ffcnc pr wk pr ln] GROUP BY [Line] PIVOT [Week];


--
Duane Hookom
MS Access MVP
--

Jan van Hooff said:
Hello Duane,
Sorry for my bad English. ( I am Dutch).
This is what I tested:
I changed the design of my query: In the field "week" I filled in as
criteria number 0440.
This means that I got 9 records in the query: for each line the quantity
of
week 0440.

Afterwards I made a chart with the reportwizard.
In the properties of the chart I filled in "line" in the master/child
properties.

This was my recordsource: TRANSFORM Sum([quantity]) AS [sumofquantity]
SELECT [Week] FROM [q ffcnc pr wk pr ln] GROUP BY [Week] PIVOT [Line];

So the chart is exactly the same as before (all 9 lines in 1 chart). Only
on
the x-axis there is only 1 point: week 0440.

Jan





Duane Hookom said:
When I suggest your report's record source should have 9 records (one for
each line) I would expect that you would try this.

--
Duane Hookom
MS Access MVP
--

Sorry Duane,
It did not.
I have 9 records for each week, for example:

week number line number quantity
1 1 1000
1 2 800
. . .
1 9 2000

and so on for each week.
So I want to see a chart with on the x-axis the weeknumber and on the
y-axis
the quantity. And this in 9 seperate charts.

Jan


:

Does your report's record source contain 9 records (1 for each line)
as
noted in the first line of my previous reply? If it did, you should
not
be
prompted for the line.

--
Duane Hookom
MS Access MVP
--

message
Hello Duane,
Thank you, but I am afraid I didn't understand it quit well. In my
query
for
every week there is a record for each line. In the chart I want to
see
the
quantity per week on each line. I have 9 lines. So I expect 9
charts,
but
I
get 1 chart, with 9 lines.
When I set in the master/child properties "line", and I view the
report
I
am
asked to fill in the parameter. When I fill in the line number then
I
see
the
chart with 1 line.
But, if it is possible, I want to see 9 charts at once.

Jan

:

Make sure your report's recordsource contains 1 record for each
line.
Then
use the Link Master/Child properties of the graph control to limit
the
lines
to just the current line.

--
Duane Hookom
MS Access MVP
--

message
Hello,
I have made a query with 3 fields: linenumber, weeknumber and
quantity.
When I make a chart in a form (with the wizard), I get for every
line a
chart.
In this chart the quantity is visible for each week.
When I try to make a chart in a report, I get all lines in 1
chart.
How can I get a chart for each line in the report, without making
different
queries?

Jan
Click to expand...
Click to expand...
Click to expand...
 
Top