Clipper2000
Programmer
Example CrossTab query:
Code:
TRANSFORM Sum([tbl90707].[Amount]) AS Amount
SELECT [NelNetRollUp].[Total], [NelNetRollUp].[SubTotal], [tblNelNetMapEdulinx].[Account], [tblNelNetMapEdulinx].[subAcct], [tbl90707].[Accpac], [tbl90707].[Description], [tblNelNetRollUpOrder].[RollUpOrder]
FROM ((tbl90707 LEFT JOIN tblNelNetMapEdulinx ON CLng([tbl90707].[Accpac])=[tblNelNetMapEdulinx].[Accpac]) LEFT JOIN NelNetRollUp ON [tblNelNetMapEdulinx].[Account] & [tblNelNetMapEdulinx].[subAcct]=[NelNetRollUp].[Acct] & [NelNetRollUp].[Sub Acct]) LEFT JOIN tblNelNetRollUpOrder ON [NelNetRollup].[Total]=[tblNelNetRollUpOrder].[Total]
WHERE ([tbl90707].[Category]="EDU" And [tbl90707].[Period]=9 And [tbl90707].[Year]=2004 And clng([tbl90707].[Accpac])>39999 Or [tbl90707].[Category]="EDU" And [tbl90707].[Period]=8 And [tbl90707].[Year]=2004 And clng([tbl90707].[Accpac])>39999 Or [tbl90707].[Category]="EDU" And [tbl90707].[Period]=7 And [tbl90707].[Year]=2004 And clng([tbl90707].[Accpac])>39999)
GROUP BY [NelNetRollUp].[Total], [NelNetRollUp].[SubTotal], [tblNelNetMapEdulinx].[Account], [tblNelNetMapEdulinx].[subAcct], [tbl90707].[Accpac], [tbl90707].[Description], [tbl90707].[Category], [tblNelNetRollUpOrder].[RollUpOrder]
ORDER BY [tblNelNetRollUpOrder].[RollUpOrder], [tblNelNetMapEdulinx].[Account], [tblNelNetMapEdulinx].[subAcct]
PIVOT (Period & Year)
This may look complicated but the end result is just 3 columns with the alaise names: 92004, 82004, 72004
Is there any way to have the query returns some alaise names for the columns such as: Actual, Budget, Forecast instead of 92004, 82004, 72004 ?
I know that if the columns happens to be the dates, then I can use the IN clause such as:
PIVOT (Month) IN (Jan, Feb, Mar, Apr ...), etc. But, is there any way to name the columns aside from dates ?
Thank you for any help in advance.