 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2007Trouble with Dates
I have a +300K set of sales history records that I now need to extract some data for demand forecasting and need to summarize specific sub-sets by month/year. I used a Month([docdate]) and Year([docdate]) function within a query to get close to what I need. I can combine the two fields in a select query and sort in proper chronological sequence, but when I build a crosstab query on top of the select query it sorts the date fields as column headings as text, i.e. 1/2005, 1/2006, 1/2007, 10/2005... Any suggestions? Thanks, Doug phaedrus12 at hotmail dot com The is no such thing as a date of 1/2007. In actuality, they are text. Add the docDate column, or a custom sorting table, depending upon how you need to sort.  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >I have a +300K set of sales history records that I now need to extract some > data for demand forecasting and need to summarize specific sub-sets by [quoted text clipped - 15 lines] > > phaedrus12 at hotmail dot com Thanks. I know that 1/2007 is not a date, however, I need to summarize my data in monthly buckets. I'm not attached to any specific format, but at the end of the process I need to see 1/2005 (or 01/2005 or Jan-2005) with the buckets sorting in chronological sequence. I was using Excel until the size of the extracted data set exceeded the 65,536 row limit... Doug > The is no such thing as a date of 1/2007. In actuality, they are text. Add > the docDate column, or a custom sorting table, depending upon how you need [quoted text clipped - 18 lines] > > > > phaedrus12 at hotmail dot com You can use Excel 2007 which handles more rows, but a database is the right tool for large datasets. What I was suggesting is to build another table and add rows like: Month_Year Sort 1/2005 1 1/2006 2 1/2007 3 2/2005 4 etc. In a new query, link the Month_Year column from the new table to the similar column from the first query. Now add the Sort column and use it for the Order By (sorting) clause. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Thanks. I know that 1/2007 is not a date, however, I need to summarize my > data in monthly buckets. I'm not attached to any specific format, but at [quoted text clipped - 36 lines] >> > >> > phaedrus12 at hotmail dot com Crosstab queries have a "Column Headings" property. By filling it in with "1/2005", "2/2005", etc. you can 1) specify the column order and 2) create placeholders columns (column 2/2005 will appear even if there is no data for that period) HTH, >I have a +300K set of sales history records that I now need to extract some > data for demand forecasting and need to summarize specific sub-sets by [quoted text clipped - 15 lines] > > phaedrus12 at hotmail dot com |
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.