Line chart report

[Joel Allen]

Hello,

I made a Line Chart report using the Chart Wizard.

In the Y axis (Data) I have [Quantity] by sum.
In the X axis (Axis)I have [Date] by month.
In the Series box I have [Date] by year.

This kind of gives me what I want, but along the X axis I have Jan '01, Feb
'01, March '01, Jan '02, Feb '02, March '02. I just want Jan, Feb, March,
and there would be two lines above it representing 2001 and 2002 that would
kind of overlap eachother to show comparison.

I hope that makes sense.

Thank you
-Joel

 

[Duane Hookom]

I think you can change your row source to a crosstab with the year as the
column heading and month number as the row heading.

 

[Joel Allen]

Here's what I have now. Can you please help?

TRANSFORM Sum([Qty]) AS [SumOfQty] SELECT (Format([Date],"MMM 'YY")) FROM
[Table1] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM
'YY")) PIVOT (Year([Date]));

THanks.

-Joel

I think you can change your row source to a crosstab with the year as the
column heading and month number as the row heading.

--
Duane Hookom
MS Access MVP

Hello,

I made a Line Chart report using the Chart Wizard.

In the Y axis (Data) I have [Quantity] by sum.
In the X axis (Axis)I have [Date] by month.
In the Series box I have [Date] by year.

This kind of gives me what I want, but along the X axis I have Jan '01,
Feb '01, March '01, Jan '02, Feb '02, March '02. I just want Jan, Feb,
March, and there would be two lines above it representing 2001 and 2002
that would kind of overlap eachother to show comparison.

I hope that makes sense.

Thank you
-Joel

 

[Duane Hookom]

Try this
TRANSFORM Sum([Qty]) AS [SumOfQty]
SELECT Format([Date],"MM")
FROM [Table1]
GROUP BY Format([Date],"MM")
PIVOT (Year([Date]));

--
Duane Hookom
MS Access MVP

Here's what I have now. Can you please help?

TRANSFORM Sum([Qty]) AS [SumOfQty] SELECT (Format([Date],"MMM 'YY")) FROM
[Table1] GROUP BY (Year([Date])*12 +
Month([Date])-1),(Format([Date],"MMM 'YY")) PIVOT (Year([Date]));

THanks.

-Joel

I think you can change your row source to a crosstab with the year as the
column heading and month number as the row heading.

--
Duane Hookom
MS Access MVP

Hello,

I made a Line Chart report using the Chart Wizard.

In the Y axis (Data) I have [Quantity] by sum.
In the X axis (Axis)I have [Date] by month.
In the Series box I have [Date] by year.

This kind of gives me what I want, but along the X axis I have Jan '01,
Feb '01, March '01, Jan '02, Feb '02, March '02. I just want Jan, Feb,
March, and there would be two lines above it representing 2001 and 2002
that would kind of overlap eachother to show comparison.

I hope that makes sense.

Thank you
-Joel