If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab By Date (Month)
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 |
#2
|
|||
|
|||
Crosstab By Date (Month)
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" wrote: 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 . |
#3
|
|||
|
|||
Crosstab By Date (Month)
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" wrote in message ... 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" wrote: 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 . |
#4
|
|||
|
|||
Crosstab By Date (Month)
I have seen post that does.
Search on Dynamic Crosstab Heading -- Build a little, test a little. "Terry" wrote: 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" wrote in message ... 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" wrote: 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 . . |
#5
|
|||
|
|||
Crosstab By Date (Month)
There is a solution that should work at
http://www.tek-tips.com/faqs.cfm?fid=5466. -- Duane Hookom Microsoft Access MVP "KARL DEWEY" wrote: I have seen post that does. Search on Dynamic Crosstab Heading -- Build a little, test a little. "Terry" wrote: 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" wrote in message ... 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" wrote: 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 . . |
#6
|
|||
|
|||
Crosstab By Date (Month)
Thx,
Works well. Appreciate the help! Terry "Duane Hookom" wrote in message ... There is a solution that should work at http://www.tek-tips.com/faqs.cfm?fid=5466. -- Duane Hookom Microsoft Access MVP "KARL DEWEY" wrote: I have seen post that does. Search on Dynamic Crosstab Heading -- Build a little, test a little. "Terry" wrote: 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" wrote in message ... 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" wrote: 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 . . |
Thread Tools | |
Display Modes | |
|
|