Link to home
Start Free TrialLog in
Avatar of avoorheis
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.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.

thanks
ASKER CERTIFIED SOLUTION
Avatar of DavidE
DavidE

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of avoorheis
avoorheis

ASKER

thanks David, very helpful, and I'll probably end up going that way.

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.