snooflehammer
asked on
Getting a crosstab query to display month columns as names in the correct order
I'm querying a table of data that contains a date, consultant, client and $ value.
I want to summarise it in a table with the first column the consultant, the second the client. The remaining columns are the months. The$ value (QuoteAmount) is the value that populates the table of results.
SQL so far:
TRANSFORM Sum(qryBudgetReportBase.Qu oteAmount) AS SumOfQuoteAmount
SELECT qryBudgetReportBase.Consul tant, qryBudgetReportBase.Client Name
FROM qryBudgetReportBase
GROUP BY qryBudgetReportBase.Consul tant, qryBudgetReportBase.Client Name
ORDER BY qryBudgetReportBase.Consul tant, qryBudgetReportBase.Client Name, Month([StartDate])
PIVOT Month([StartDate]);
I've attached a shot of the results without the first two columns because they are identifiable.
Problems:
The column headers are month numbers. I need month names.
Months with no data do not appear. I need empty months.
The months are in Jan-Dec order, I need them in the order of the months from the start of the dataset, in this case July, so they should read left to right from Jul-Jun
Crosstab.JPG
I want to summarise it in a table with the first column the consultant, the second the client. The remaining columns are the months. The$ value (QuoteAmount) is the value that populates the table of results.
SQL so far:
TRANSFORM Sum(qryBudgetReportBase.Qu
SELECT qryBudgetReportBase.Consul
FROM qryBudgetReportBase
GROUP BY qryBudgetReportBase.Consul
ORDER BY qryBudgetReportBase.Consul
PIVOT Month([StartDate]);
I've attached a shot of the results without the first two columns because they are identifiable.
Problems:
The column headers are month numbers. I need month names.
Months with no data do not appear. I need empty months.
The months are in Jan-Dec order, I need them in the order of the months from the start of the dataset, in this case July, so they should read left to right from Jul-Jun
Crosstab.JPG
Will the dataset always start from July or does this change?
ASKER
Always from July, but I'll need to specify the year
ASKER
Ignore the year specification. I've already isolated the date range in the subset of data I'm working with
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to modify it slightly, but it essentially did the job:
TRANSFORM Sum(qryBudgetReportBase.Qu oteAmount) AS SumOfQuoteAmount
SELECT qryBudgetReportBase.Consul tant, qryBudgetReportBase.Client Name
FROM qryBudgetReportBase
GROUP BY qryBudgetReportBase.Consul tant, qryBudgetReportBase.Client Name
ORDER BY qryBudgetReportBase.Consul tant, qryBudgetReportBase.Client Name
PIVOT Format([StartDate],"mmm") In ("Jul","Aug","Sep","Oct"," Nov","Dec" ,"Jan","Fe b","Mar"," Apr","May" ,"Jun");
Thank you
TRANSFORM Sum(qryBudgetReportBase.Qu
SELECT qryBudgetReportBase.Consul
FROM qryBudgetReportBase
GROUP BY qryBudgetReportBase.Consul
ORDER BY qryBudgetReportBase.Consul
PIVOT Format([StartDate],"mmm") In ("Jul","Aug","Sep","Oct","
Thank you
PIVOT Format([StartDate],"mmm") In ("Jul","Aug","Sep","Oct","
but i suggest you need to add the year in your header i.e., 2014 Jul, 2014 Aug ....