M
mscertified
I have all the data I need for my chart in a table. I've tried using the
chart wizard to produce the chart I need and after half-a dozen attempts am
very frustrated. I need a line chart where each line represents a different
column in my table.
Basically the data is rows representing different weeks figures. The columns
are the values for open, closed and pending issues. So I need the x and y
axis to be the number of issues and the week-ending dates. The lines need to
be the figures for open, closed and pending. How do I tell the wizard to do
this? Here is the current Rowsource generated by the wizard (nothing like
what I want). I'm having a hard time envisaging how this rowsource relates to
the chart.
TRANSFORM Sum(tblRptTSCACE.Open) AS SumOfOpen
SELECT (Format([DateTo],"ww"" '""yy")) AS Expr1
FROM tblRptTSCACE
GROUP BY (Year([DateTo])*CLng(54)+DatePart("ww",[DateTo],0)-1),
(Format([DateTo],"ww"" '""yy"))
PIVOT Format([DateTo],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
chart wizard to produce the chart I need and after half-a dozen attempts am
very frustrated. I need a line chart where each line represents a different
column in my table.
Basically the data is rows representing different weeks figures. The columns
are the values for open, closed and pending issues. So I need the x and y
axis to be the number of issues and the week-ending dates. The lines need to
be the figures for open, closed and pending. How do I tell the wizard to do
this? Here is the current Rowsource generated by the wizard (nothing like
what I want). I'm having a hard time envisaging how this rowsource relates to
the chart.
TRANSFORM Sum(tblRptTSCACE.Open) AS SumOfOpen
SELECT (Format([DateTo],"ww"" '""yy")) AS Expr1
FROM tblRptTSCACE
GROUP BY (Year([DateTo])*CLng(54)+DatePart("ww",[DateTo],0)-1),
(Format([DateTo],"ww"" '""yy"))
PIVOT Format([DateTo],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");