 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / April 2007Sort by month and year in chart
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]; 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"  SignatureDuane Hookom Microsoft Access MVP > Hello, > [quoted text clipped - 27 lines] > > PIVOT qryCategoryChartPeriod.[By Month]; 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, > [quoted text clipped - 27 lines] > > PIVOT qryCategoryChartPeriod.[By Month]; 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. SignatureDuane Hookom Microsoft Access MVP > Hi, > I started adding a column to my queries that creates a number out of the [quoted text clipped - 39 lines] > > > > PIVOT qryCategoryChartPeriod.[By Month]; 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 [quoted text clipped - 43 lines] > > > > > > PIVOT qryCategoryChartPeriod.[By Month]; That makes more sense. I would probably use Year(MyDate) + Month(MyDate)/100 Whatever works.... SignatureDuane Hookom Microsoft Access MVP > I'm sorry, I multiply by 0.01 to get the format yyyy.mm. This sorts in the > proper order. [quoted text clipped - 46 lines] > > > > > > > > PIVOT qryCategoryChartPeriod.[By Month]; |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.