Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cross Tab queries: How to change the column name with an alaise name

Status
Not open for further replies.

Clipper2000

Programmer
Nov 28, 2004
54
0
0
US

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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top