Crosstab help needed

[scubadiver]

I have a source SQL:

SELECT LiveMonth.MonthID, LiveMonth.LiveMonth, AccStat.Count AS Total,
AccStat.ASID, [Account Status].AccountStat
FROM LiveMonth INNER JOIN ([Account Status] INNER JOIN AccStat ON [Account
Status].ASID = AccStat.ASID) ON LiveMonth.MonthID = AccStat.MonthID;



From that I have this (which calculates percentage change for each account
status)


SELECT accountsrc.ASID, accountsrc.LiveMonth, accountsrc.AccountStat,

(SELECT Max([Total])
FROM accountsrc AS temp
WHERE temp.ASID = accountsrc.ASID and temp.monthID=accountsrc.monthID-1) AS
Old,

accountsrc.Total AS New, (([new]-[old])/[old]) AS perc

FROM accountsrc

WHERE

((((SELECT Max([Total])
FROM accountsrc AS temp
WHERE temp.ASID = accountsrc.ASID and temp.monthID=accountsrc.monthID-1)) Is
Not Null));


Now I want to create a cross-tab with Month as row heading, account status
as column heading and the percentage in the cells. But I get an error message
saying
'accountsrc.ASID' not recognised.

What is the easiest way to resolve this?

Thanks

 

[KARL DEWEY]

You will need to make a temporary table for the data to use as source for the
crosstab. A crosstab will not work with an alias like - FROM accountsrc AS
temp