Change column names in Crosstab 1

[dhborchardt]

I need to change the names of the two column headings for [Primary earning code]. There are only two possibilities: 1 and 2. I would like 1 to say FTE1 and 2 to say FTE2. This is needed to interface another DB for running reports.

TRANSFORM Sum(Sheet1.TotalHours) AS SumOfTotalHours
SELECT Sheet1.JobCode, Sum(Sheet1.TotalHours) AS [Total Of TotalHours]
FROM Sheet1
GROUP BY Sheet1.JobCode
PIVOT Sheet1.[Primary Earning Code];

 

[dhookom]

Create a small table with the code and the title. Add this to your crosstab query so you can use the title as the column heading.

TRANSFORM Sum(Sheet1.TotalHours) AS SumOfTotalHours
SELECT Sheet1.JobCode, Sum(Sheet1.TotalHours) AS [Total Of TotalHours]
FROM Sheet1 JOIN tblEarningCodes on Sheet1.[Primary Earning Code]=tblEarningCodes.Code
GROUP BY Sheet1.JobCode
PIVOT tblEarningCodes.CodeTitle;

You could also use IIf() or Switch() or Choose() to replace the number with a title but I much prefer using data rather than expressions.

Duane
Hook'D on Access
MS Access MVP

 

[dhborchardt]

Thanks
I created the extra table like so:

CODE / Title
______________
1 FTE1
2 FTE2

and changed the SQL like you showed. I didn't use the same table name. When I run it, I get a syntax error. When I click OK it highlights the JOIN word.

TRANSFORM Sum(Sheet1.TotalHours) AS SumOfTotalHours
SELECT Sheet1.JobCode, Sum(Sheet1.TotalHours) AS [Total Of TotalHours]
FROM Sheet1 JOIN tblAliasPEC on Sheet1.[Primary Earning Code] = tblAliasPEC.Code
GROUP BY Sheet1.JobCode
PIVOT tblAliasPEC.Title;

 

[dhookom]

This might need to be INNER JOIN in Access queries:

TRANSFORM Sum(Sheet1.TotalHours) AS SumOfTotalHours
SELECT Sheet1.JobCode, Sum(Sheet1.TotalHours) AS [Total Of TotalHours]
FROM Sheet1 INNER JOIN tblAliasPEC on Sheet1.[Primary Earning Code] = tblAliasPEC.Code
GROUP BY Sheet1.JobCode
PIVOT tblAliasPEC.Title;

Duane
Hook'D on Access
MS Access MVP

 

[dhborchardt]

Bingo - I guess I should have caught that. I also noticed that I posted in the wrong Forum. I must have mis-clicked.
Thanks a lot for the help.