avoorheis
asked on
Dynamic labels in a crosstab query used in calculation
I need to do some calculations on some monthly totals which are labeled with the month and year. I'll be doing this monthly, so, the labels will change (like 8/07, 9/07, 10/07 then 9/07, 10/07, 11/07).
Currently, I'm using a crosstab query to populate a spreadsheet with this data, I need to the data corresponding to the months in a single row for each record.
I'm trying to figure out a way to compare the last 2 months of data, but, since the labels change each month, I'm not sure how to accomplish this.
Using the crosstab as a subquery, the query where I want to do the calculations looks something like:
SELECT DISTINCT tblSEODashBoardData.WEB_ID , tblSEODashBoardData.ActDt, qrySEODashBoardDataChart.[ 05/07], qrySEODashBoardDataChart.[ 06/07], qrySEODashBoardDataChart.[ 07/07], qrySEODashBoardDataChart.[ 08/07], qrySEODashBoardDataChart.[ 09/07], qrySEODashBoardDataChart.[ 10/07], ([10/07]/[09/07])-1 AS ChangeFromLast
FROM tblSEODashBoardData LEFT JOIN qrySEODashBoardDataChart ON tblSEODashBoardData.WEB_ID = qrySEODashBoardDataChart.W EB_ID
WHERE (((tblSEODashBoardData.WEB _ID) Is Not Null))
ORDER BY tblSEODashBoardData.WEB_ID ;
So, in this case I need to figure out what to use for labels instead of the mm/yy, or maybe there a better way to handle this.
thanks
Currently, I'm using a crosstab query to populate a spreadsheet with this data, I need to the data corresponding to the months in a single row for each record.
I'm trying to figure out a way to compare the last 2 months of data, but, since the labels change each month, I'm not sure how to accomplish this.
Using the crosstab as a subquery, the query where I want to do the calculations looks something like:
SELECT DISTINCT tblSEODashBoardData.WEB_ID
FROM tblSEODashBoardData LEFT JOIN qrySEODashBoardDataChart ON tblSEODashBoardData.WEB_ID
WHERE (((tblSEODashBoardData.WEB
ORDER BY tblSEODashBoardData.WEB_ID
So, in this case I need to figure out what to use for labels instead of the mm/yy, or maybe there a better way to handle this.
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Although, I'm now very curious if there actually is a way to relabel or rename the those column headings. Seems like this sort of thing would come up often.