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.WEB_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.