Crosstab By Date (Month)

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I need to write a crosstab on a shipping table that has the Customer as the
row heading, month as the column heading, and sum of shipped as the value.
The crosstab wizard does this easy enough, but my problem is I need to have
the column heads (month) sort by year also. If I use the wizard and run a
crosstab on the last 6 months, it still sorts the colomns as Jan, Feb,
Mar,.......Dec. What I am going for is ....Sept 09, Oct 09, Nov09, Dec09,
Jan10, Feb10.

The data is correct the first way - just sorted wrong. Is there a way to do
this?
Thanks
Terry
 
In the SQL view for the PIVOT statement use this with your field name --
PIVOT Format([YourDield], "mmm yy") IN("Sep 09", "Oct 09", "Nov 09",
"Dec09", "Jan 10", "Feb 10", "Mar 10")

The IN part will produce column names in the same order. But the FORMAT
must output the date in the exact manner as in the IN part.
 
Is there a way to do this so that it is dynamic? I need to run this query
for differant time frames, and run it each month. It would be cumbersome to
have to change the "IN" statement each time.
Thanks for the help.

Terry
KARL DEWEY said:
In the SQL view for the PIVOT statement use this with your field name --
PIVOT Format([YourDield], "mmm yy") IN("Sep 09", "Oct 09", "Nov 09",
"Dec09", "Jan 10", "Feb 10", "Mar 10")

The IN part will produce column names in the same order. But the FORMAT
must output the date in the exact manner as in the IN part.

--
Build a little, test a little.


Terry said:
I need to write a crosstab on a shipping table that has the Customer as
the
row heading, month as the column heading, and sum of shipped as the
value.
The crosstab wizard does this easy enough, but my problem is I need to
have
the column heads (month) sort by year also. If I use the wizard and run a
crosstab on the last 6 months, it still sorts the colomns as Jan, Feb,
Mar,.......Dec. What I am going for is ....Sept 09, Oct 09, Nov09, Dec09,
Jan10, Feb10.

The data is correct the first way - just sorted wrong. Is there a way to
do
this?
Thanks
Terry


.
Click to expand...
Click to expand...
 
I have seen post that does.
Search on Dynamic Crosstab Heading

--
Build a little, test a little.


Terry said:
Is there a way to do this so that it is dynamic? I need to run this query
for differant time frames, and run it each month. It would be cumbersome to
have to change the "IN" statement each time.
Thanks for the help.

Terry
KARL DEWEY said:
In the SQL view for the PIVOT statement use this with your field name --
PIVOT Format([YourDield], "mmm yy") IN("Sep 09", "Oct 09", "Nov 09",
"Dec09", "Jan 10", "Feb 10", "Mar 10")

The IN part will produce column names in the same order. But the FORMAT
must output the date in the exact manner as in the IN part.

--
Build a little, test a little.


Terry said:
I need to write a crosstab on a shipping table that has the Customer as
the
row heading, month as the column heading, and sum of shipped as the
value.
The crosstab wizard does this easy enough, but my problem is I need to
have
the column heads (month) sort by year also. If I use the wizard and run a
crosstab on the last 6 months, it still sorts the colomns as Jan, Feb,
Mar,.......Dec. What I am going for is ....Sept 09, Oct 09, Nov09, Dec09,
Jan10, Feb10.

The data is correct the first way - just sorted wrong. Is there a way to
do
this?
Thanks
Terry


.
Click to expand...
Click to expand...


.
Click to expand...
 
There is a solution that should work at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP


KARL DEWEY said:
I have seen post that does.
Search on Dynamic Crosstab Heading

--
Build a little, test a little.


Terry said:
Is there a way to do this so that it is dynamic? I need to run this query
for differant time frames, and run it each month. It would be cumbersome to
have to change the "IN" statement each time.
Thanks for the help.

Terry
KARL DEWEY said:
In the SQL view for the PIVOT statement use this with your field name --
PIVOT Format([YourDield], "mmm yy") IN("Sep 09", "Oct 09", "Nov 09",
"Dec09", "Jan 10", "Feb 10", "Mar 10")

The IN part will produce column names in the same order. But the FORMAT
must output the date in the exact manner as in the IN part.

--
Build a little, test a little.


:

I need to write a crosstab on a shipping table that has the Customer as
the
row heading, month as the column heading, and sum of shipped as the
value.
The crosstab wizard does this easy enough, but my problem is I need to
have
the column heads (month) sort by year also. If I use the wizard and run a
crosstab on the last 6 months, it still sorts the colomns as Jan, Feb,
Mar,.......Dec. What I am going for is ....Sept 09, Oct 09, Nov09, Dec09,
Jan10, Feb10.

The data is correct the first way - just sorted wrong. Is there a way to
do
this?
Thanks
Terry


.
Click to expand...


.
Click to expand...
Click to expand...
 
Thx,
Works well. Appreciate the help!
Terry
Duane Hookom said:
There is a solution that should work at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP


KARL DEWEY said:
I have seen post that does.
Search on Dynamic Crosstab Heading

--
Build a little, test a little.


Terry said:
Is there a way to do this so that it is dynamic? I need to run this
query
for differant time frames, and run it each month. It would be
cumbersome to
have to change the "IN" statement each time.
Thanks for the help.

Terry
In the SQL view for the PIVOT statement use this with your field
name --
PIVOT Format([YourDield], "mmm yy") IN("Sep 09", "Oct 09", "Nov 09",
"Dec09", "Jan 10", "Feb 10", "Mar 10")

The IN part will produce column names in the same order. But the
FORMAT
must output the date in the exact manner as in the IN part.

--
Build a little, test a little.


:

I need to write a crosstab on a shipping table that has the Customer
as
the
row heading, month as the column heading, and sum of shipped as the
value.
The crosstab wizard does this easy enough, but my problem is I need
to
have
the column heads (month) sort by year also. If I use the wizard and
run a
crosstab on the last 6 months, it still sorts the colomns as Jan,
Feb,
Mar,.......Dec. What I am going for is ....Sept 09, Oct 09, Nov09,
Dec09,
Jan10, Feb10.

The data is correct the first way - just sorted wrong. Is there a
way to
do
this?
Thanks
Terry


.



.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top