Multi series/lines chart in ms access

Y

Yaser

hi everyone,
I have problem creating a line chart with more than one series/line. My
table looks like:
ID, CI1,CI2,CI3,PI1,PI2,PI3
I want to make a chart for each ID that consist of two line, the first line
plot CI1 ..CI3 and
the second line plots PI1 .. PI3.
In the recordset source I have a SELECT statment that return all the
required data, but the chart plots all the data in the same line (I tried to
change the datasheet to plot two lines but it didn't work).

Is there a way to force the chart to draw 2 lines instead of one and how I
can specify the source for each line.

Any help is appreciated

thanks in advance
 
J

Jerry Porter

Yaser,

The select statement for your chart needs to return 3 columns, one for
ID, and one for each line you want in your chart. I'd need clearer
details on your table structure to give any suggestions. What are the
fields?

Jerry
 
Y

Yaser

Jerry,
thnx for the reply.

my table fields ar all of the same type (integers), the fields are:
ID, CI1,CI2,CI3,PI1,PI2,PI3
the table looks like
ID CI1 CI2 CI3 PI1 PI2 PI3
1 1.5 1.4 2.0 2.0 2.1 1.5
2 1.7 2.0 3.2 1.0 1.6 1.0
3 2.0 2.7 1.5 2.1 3.6 4.0
..
..
..

I need to plot two lines, one for CIs and one for PIs for each ID in a report
my current SELECT statment looks like
SELECT ID, CI1,CI2,CI3,PI1,PI2,PI3 from table_main
the figure is in a form which I then add to the report using subform control
and link the report and the form/graph using the ID. So far the only figure I
got is single lineded (if such word exist lol)

I guess from ur answer I need to modify my SELECT statment to something like
SELECT ID,Line1, Line2 from table_main
such that line1 gets the CIs and Line2 gets the PIs.

please let me know if i am in the right track

thanx for the help again
 
J

Jerry Porter

Yaser,

I think it's pretty tricky, given the way your data is structured. Your
understanding of what you need is correct.

Here's a query that might get you closer:

Select ID, 1 As ItemIndex, CI1 As CI, PI1 As PI from table_main
Union
Select ID, 2 As ItemIndex, CI2 As CI, PI2 As PI from table_main
Union
Select ID, 3 As ItemIndex, CI3 As CI, PI3 As PI from table_main

This is a union query made up of 3 queries on tip of each other. This
will get all your CI's into one column, and all your PI's into one
column. It should transform your data into:

ID ItemIndex CI PI
1 1 1.5 2.0
1 2 1.4 2.1
1 3 2.0 1.5
2 1 1.7 1.0
2 2 2.0 1.6
2 3 3.2 1.0
3 1 2.0 2.1
3 2 2.7 3.6
3 3 1.5 4.0

You might have to do some more fiddling to select rows for one id from
this query.

The ItemIndex field in the query might not be necessary. But it
perserves the information of which field the data came from, and might
be needed for sorting at some point.

Jerry
 
J

Jerry Porter

I should mention that a better solution might be to restructure your
table so that it's in this structure in the first place. (Normalized
data).

Jerry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top