Sort by month and year in chart

[CAM]

Hello,

I have a bar chart that shows inventory by category and by month: For
example

-------------------------------------- By
Month ------------------------------------------------
Category Level 1 August 2006 September 2006 October 2006 November
2006 December 2006 January 2007
Software 8 9
10 8 20 15
Hardware 6 4
5 4 11 21

The problem is that in my legend and graph I can't seem to get the months
display correctly I get August 2006, December 2006,..... I don't want
that. I want to display as August 2006, September 2006 .......- January
2007 How can I get the month and year in the correct order? Below is my
code. Any tips or websit to visit will be appreciated. Thank you in
advance.


TRANSFORM Sum(qryCategoryChartPeriod.[CountOfCategory Level 1]) AS
SumOfCategory

SELECT qryCategoryChartPeriod.[Category Level 1]

FROM qryCategoryChartPeriod

GROUP BY qryCategoryChartPeriod.[Category Level 1]

PIVOT qryCategoryChartPeriod.[By Month];

 

[Guest]

This will always display in alpha order by your value in [By Month]. You can
add your potential column headings into the Column Headings property.
Column Headings: "August 2006", "September 2006" .......- "January 2007"

 

[Guest]

Hi,
I started adding a column to my queries that creates a number out of the
month and year that looks like yyyy.mm using the formula: datepart("yyyy",
mydate)+(datepart("m", mydate)*100). I put this column into
ascending/descending order in the query, but use the field with the monthname
on the chart.

One of my coworkers turns the date into YYMM numeric field.
I'm not sure which works better....

Hope this help.

 

[Guest]

It isn't clear if you are the same poster as "CAM" but I don't know why you
would multiply the month number * 100 and add it to the year. I would expect
YYYYMM or YYMM to work better.

--
Duane Hookom
Microsoft Access MVP

Hi,
I started adding a column to my queries that creates a number out of the
month and year that looks like yyyy.mm using the formula: datepart("yyyy",
mydate)+(datepart("m", mydate)*100). I put this column into
ascending/descending order in the query, but use the field with the monthname
on the chart.

One of my coworkers turns the date into YYMM numeric field.
I'm not sure which works better....

Hope this help.

Hello,

I have a bar chart that shows inventory by category and by month: For
example

-------------------------------------- By
Month ------------------------------------------------
Category Level 1 August 2006 September 2006 October 2006 November
2006 December 2006 January 2007
Software 8 9
10 8 20 15
Hardware 6 4
5 4 11 21

The problem is that in my legend and graph I can't seem to get the months
display correctly I get August 2006, December 2006,..... I don't want
that. I want to display as August 2006, September 2006 .......- January
2007 How can I get the month and year in the correct order? Below is my
code. Any tips or websit to visit will be appreciated. Thank you in
advance.


TRANSFORM Sum(qryCategoryChartPeriod.[CountOfCategory Level 1]) AS
SumOfCategory

SELECT qryCategoryChartPeriod.[Category Level 1]

FROM qryCategoryChartPeriod

GROUP BY qryCategoryChartPeriod.[Category Level 1]

PIVOT qryCategoryChartPeriod.[By Month];

 

[Guest]

I'm sorry, I multiply by 0.01 to get the format yyyy.mm. This sorts in the
proper order.

It isn't clear if you are the same poster as "CAM" but I don't know why you
would multiply the month number * 100 and add it to the year. I would expect
YYYYMM or YYMM to work better.

--
Duane Hookom
Microsoft Access MVP

Hi,
I started adding a column to my queries that creates a number out of the
month and year that looks like yyyy.mm using the formula: datepart("yyyy",
mydate)+(datepart("m", mydate)*100). I put this column into
ascending/descending order in the query, but use the field with the monthname
on the chart.

One of my coworkers turns the date into YYMM numeric field.
I'm not sure which works better....

Hope this help.

Hello,

I have a bar chart that shows inventory by category and by month: For
example

-------------------------------------- By
Month ------------------------------------------------
Category Level 1 August 2006 September 2006 October 2006 November
2006 December 2006 January 2007
Software 8 9
10 8 20 15
Hardware 6 4
5 4 11 21

The problem is that in my legend and graph I can't seem to get the months
display correctly I get August 2006, December 2006,..... I don't want
that. I want to display as August 2006, September 2006 .......- January
2007 How can I get the month and year in the correct order? Below is my
code. Any tips or websit to visit will be appreciated. Thank you in
advance.


TRANSFORM Sum(qryCategoryChartPeriod.[CountOfCategory Level 1]) AS
SumOfCategory

SELECT qryCategoryChartPeriod.[Category Level 1]

FROM qryCategoryChartPeriod

GROUP BY qryCategoryChartPeriod.[Category Level 1]

PIVOT qryCategoryChartPeriod.[By Month];

 

[Guest]

That makes more sense. I would probably use
Year(MyDate) + Month(MyDate)/100

Whatever works....
--
Duane Hookom
Microsoft Access MVP

I'm sorry, I multiply by 0.01 to get the format yyyy.mm. This sorts in the
proper order.

It isn't clear if you are the same poster as "CAM" but I don't know why you
would multiply the month number * 100 and add it to the year. I would expect
YYYYMM or YYMM to work better.

--
Duane Hookom
Microsoft Access MVP

Hi,
I started adding a column to my queries that creates a number out of the
month and year that looks like yyyy.mm using the formula: datepart("yyyy",
mydate)+(datepart("m", mydate)*100). I put this column into
ascending/descending order in the query, but use the field with the monthname
on the chart.

One of my coworkers turns the date into YYMM numeric field.
I'm not sure which works better....

Hope this help.

:

Hello,

I have a bar chart that shows inventory by category and by month: For
example

-------------------------------------- By
Month ------------------------------------------------
Category Level 1 August 2006 September 2006 October 2006 November
2006 December 2006 January 2007
Software 8 9
10 8 20 15
Hardware 6 4
5 4 11 21

The problem is that in my legend and graph I can't seem to get the months
display correctly I get August 2006, December 2006,..... I don't want
that. I want to display as August 2006, September 2006 .......- January
2007 How can I get the month and year in the correct order? Below is my
code. Any tips or websit to visit will be appreciated. Thank you in
advance.


TRANSFORM Sum(qryCategoryChartPeriod.[CountOfCategory Level 1]) AS
SumOfCategory

SELECT qryCategoryChartPeriod.[Category Level 1]

FROM qryCategoryChartPeriod

GROUP BY qryCategoryChartPeriod.[Category Level 1]

PIVOT qryCategoryChartPeriod.[By Month];