Trouble with Dates

[Guest]

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

 

[Arvin Meyer [MVP]]

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.

 

[Guest]

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
to sort.
--
Arvin 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
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

 

[George Nicholson]

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,

 

[Arvin Meyer [MVP]]

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.
--
Arvin 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
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
to sort.
--
Arvin 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
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