Crosstab Query Column headings

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I have a crosstab query with 6 row heading fields and then the field
[FTE_DATE_YM] as the column heading and the field [Hours] as value which is a
sum. The FTE_DATE_YM field is a series of 12 months in this format 200801,
200802, 200803 etc. The query works fine.

The issue I am having is I want the column heading to be consistent no
matter the month date so I set query properties column heading to this:
"Month 1","Month 2","Month 3","Month 4","Month 5","Month 6","Month 7","Month
8","Month 9","Month 10","Month 11","Month 12"

When I do this the column heading appear as I want but then the [Hours]
value field which is a sum no longer shows any numbers. I do have a Total of
Hours row field which does show the correct total of hours for the months but
the individual months have no values.
 
Kevin said:
I have a crosstab query with 6 row heading fields and then the field
[FTE_DATE_YM] as the column heading and the field [Hours] as value which is a
sum. The FTE_DATE_YM field is a series of 12 months in this format 200801,
200802, 200803 etc. The query works fine.

The issue I am having is I want the column heading to be consistent no
matter the month date so I set query properties column heading to this:
"Month 1","Month 2","Month 3","Month 4","Month 5","Month 6","Month 7","Month
8","Month 9","Month 10","Month 11","Month 12"

When I do this the column heading appear as I want but then the [Hours]
value field which is a sum no longer shows any numbers. I do have a Total of
Hours row field which does show the correct total of hours for the months but
the individual months have no values.
Click to expand...

here is a little more detail:

So in the [FTE_DATE_YM] field the regular data will show column headings as
such: 200806, 200807, 200808, 200809, 200810, 200811, 200812, 200901,
200902, 20003, 200904, 200905. It will always show 12 months based on the
current date minus 6 months and plus 6 months so the [FTE_DATE_YM] column
heading will vary everytime I run the query. So next month it would show
200807 first and add 200906 at the end.

What I want to see is consistent column heading "Month 1","Month 2","Month
3","Month 4","Month 5","Month 6","Month 7","Month 8","Month 9","Month
10","Month 11","Month 12" regardless of the months represented since they
will always be changing. So in this example 200806 would be Month 1 and
200807 would be Month 2 and 200808 would be Month 3 and so on...

So how do I get [FTE_DATE_YM] to be dynamic and always changing but need to
match the Month # format and allow my Hours field to show the sums.
 
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

You need to change your PIVOT clause to generate Month 1 etc. That could be
as simple as

PIVOT "Month " & Val(Mid(FTE_Date_YM,5)) IN ("Month 1", "Month 2", ...)

or it may be a bit more complex and require you to use something more complex.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Here is the code i copied. I do not understand code so if this will require
code I would appreciate if you send the code for me to drop in. I can make
changes to the column heading property i do know where that is. thanks and I
apprecaite all replies.

TRANSFORM Sum(tbl_DB_Supply_Demand_Reporting.HOURS) AS SumOfHOURS
SELECT tbl_DB_Supply_Demand_Reporting.DATA_TYPE,
tbl_DB_Supply_Demand_Reporting.DATA_ID,
tbl_DB_Supply_Demand_Reporting.USERNAME,
tbl_DB_Supply_Demand_Reporting.LOGON_IDENTIFIER,
tbl_DB_Supply_Demand_Reporting.EMPLOYEE_TYPE,
tbl_DB_Supply_Demand_Reporting.MANAGER_USER_NAME,
tbl_DB_Supply_Demand_Reporting.ePRO_STATUS,
tbl_DB_Supply_Demand_Reporting.ePRO_CATEGORY,
tbl_DB_Supply_Demand_Reporting.CLIENT_ID,
tbl_DB_Supply_Demand_Reporting.CLIENT_NAME,
tbl_DB_Supply_Demand_Reporting.[DEMAND RSC POOL],
tbl_DB_Supply_Demand_Reporting.[SUPPLY RSC POOL],
tbl_DB_Supply_Demand_Reporting.ORGANIZATION,
tbl_DB_Supply_Demand_Reporting.FUNCTION,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_ID,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_NAME,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_START_DATE,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_END_DATE,
tbl_DB_Supply_Demand_Reporting.PROJECT_ID,
tbl_DB_Supply_Demand_Reporting.PROJECT_NAME,
tbl_DB_Supply_Demand_Reporting.PROJECT_CATEGORY,
tbl_DB_Supply_Demand_Reporting.SKILL_NAME,
tbl_DB_Supply_Demand_Reporting.STAFFED_STATUS,
tbl_DB_Supply_Demand_Reporting.RESOURCE_POOL,
tbl_DB_Supply_Demand_Reporting.UNIT,
tbl_DB_Supply_Demand_Reporting.OPS_CLIENT_FOCUS_TEAM,
Sum(tbl_DB_Supply_Demand_Reporting.HOURS) AS [Total Of HOURS]
FROM tbl_DB_Supply_Demand_Reporting
GROUP BY tbl_DB_Supply_Demand_Reporting.DATA_TYPE,
tbl_DB_Supply_Demand_Reporting.DATA_ID,
tbl_DB_Supply_Demand_Reporting.USERNAME,
tbl_DB_Supply_Demand_Reporting.LOGON_IDENTIFIER,
tbl_DB_Supply_Demand_Reporting.EMPLOYEE_TYPE,
tbl_DB_Supply_Demand_Reporting.MANAGER_USER_NAME,
tbl_DB_Supply_Demand_Reporting.ePRO_STATUS,
tbl_DB_Supply_Demand_Reporting.ePRO_CATEGORY,
tbl_DB_Supply_Demand_Reporting.CLIENT_ID,
tbl_DB_Supply_Demand_Reporting.CLIENT_NAME,
tbl_DB_Supply_Demand_Reporting.[DEMAND RSC POOL],
tbl_DB_Supply_Demand_Reporting.[SUPPLY RSC POOL],
tbl_DB_Supply_Demand_Reporting.ORGANIZATION,
tbl_DB_Supply_Demand_Reporting.FUNCTION,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_ID,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_NAME,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_START_DATE,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_END_DATE,
tbl_DB_Supply_Demand_Reporting.PROJECT_ID,
tbl_DB_Supply_Demand_Reporting.PROJECT_NAME,
tbl_DB_Supply_Demand_Reporting.PROJECT_CATEGORY,
tbl_DB_Supply_Demand_Reporting.SKILL_NAME,
tbl_DB_Supply_Demand_Reporting.STAFFED_STATUS,
tbl_DB_Supply_Demand_Reporting.RESOURCE_POOL,
tbl_DB_Supply_Demand_Reporting.UNIT,
tbl_DB_Supply_Demand_Reporting.OPS_CLIENT_FOCUS_TEAM
PIVOT tbl_DB_Supply_Demand_Reporting.FTE_DATE_YM In ("Month 1","Month
2","Month 3","Month 4","Month 5","Month 6","Month 7","Month 8","Month
9","Month 10","Month 11","Month 12");


John Spencer said:
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

You need to change your PIVOT clause to generate Month 1 etc. That could be
as simple as

PIVOT "Month " & Val(Mid(FTE_Date_YM,5)) IN ("Month 1", "Month 2", ...)

or it may be a bit more complex and require you to use something more complex.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a crosstab query with 6 row heading fields and then the field
[FTE_DATE_YM] as the column heading and the field [Hours] as value which is a
sum. The FTE_DATE_YM field is a series of 12 months in this format 200801,
200802, 200803 etc. The query works fine.

The issue I am having is I want the column heading to be consistent no
matter the month date so I set query properties column heading to this:
"Month 1","Month 2","Month 3","Month 4","Month 5","Month 6","Month 7","Month
8","Month 9","Month 10","Month 11","Month 12"

When I do this the column heading appear as I want but then the [Hours]
value field which is a sum no longer shows any numbers. I do have a Total of
Hours row field which does show the correct total of hours for the months but
the individual months have no values.
Click to expand...
Click to expand...
 
Assuming that you have only 12 months then my earlier suggestion for modifying
the query should work.

PIVOT "Month " & Val(Mid(FTE_Date_YM,5)) IN ("Month 1", "Month 2", ...)

OR to make it work a little better you could change the column titles to have
leading zeroes.

PIVOT "Month " & Mid(FTE_Date_YM,5)
IN ("Month 01", "Month 02", ..., "Month 11", "Month 12")

When you specify the column titles then those columns will appear and no others.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Here is the code i copied. I do not understand code so if this will require
code I would appreciate if you send the code for me to drop in. I can make
changes to the column heading property i do know where that is. thanks and I
apprecaite all replies.

TRANSFORM Sum(tbl_DB_Supply_Demand_Reporting.HOURS) AS SumOfHOURS
SELECT tbl_DB_Supply_Demand_Reporting.DATA_TYPE,
tbl_DB_Supply_Demand_Reporting.DATA_ID,
tbl_DB_Supply_Demand_Reporting.USERNAME,
tbl_DB_Supply_Demand_Reporting.LOGON_IDENTIFIER,
tbl_DB_Supply_Demand_Reporting.EMPLOYEE_TYPE,
tbl_DB_Supply_Demand_Reporting.MANAGER_USER_NAME,
tbl_DB_Supply_Demand_Reporting.ePRO_STATUS,
tbl_DB_Supply_Demand_Reporting.ePRO_CATEGORY,
tbl_DB_Supply_Demand_Reporting.CLIENT_ID,
tbl_DB_Supply_Demand_Reporting.CLIENT_NAME,
tbl_DB_Supply_Demand_Reporting.[DEMAND RSC POOL],
tbl_DB_Supply_Demand_Reporting.[SUPPLY RSC POOL],
tbl_DB_Supply_Demand_Reporting.ORGANIZATION,
tbl_DB_Supply_Demand_Reporting.FUNCTION,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_ID,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_NAME,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_START_DATE,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_END_DATE,
tbl_DB_Supply_Demand_Reporting.PROJECT_ID,
tbl_DB_Supply_Demand_Reporting.PROJECT_NAME,
tbl_DB_Supply_Demand_Reporting.PROJECT_CATEGORY,
tbl_DB_Supply_Demand_Reporting.SKILL_NAME,
tbl_DB_Supply_Demand_Reporting.STAFFED_STATUS,
tbl_DB_Supply_Demand_Reporting.RESOURCE_POOL,
tbl_DB_Supply_Demand_Reporting.UNIT,
tbl_DB_Supply_Demand_Reporting.OPS_CLIENT_FOCUS_TEAM,
Sum(tbl_DB_Supply_Demand_Reporting.HOURS) AS [Total Of HOURS]
FROM tbl_DB_Supply_Demand_Reporting
GROUP BY tbl_DB_Supply_Demand_Reporting.DATA_TYPE,
tbl_DB_Supply_Demand_Reporting.DATA_ID,
tbl_DB_Supply_Demand_Reporting.USERNAME,
tbl_DB_Supply_Demand_Reporting.LOGON_IDENTIFIER,
tbl_DB_Supply_Demand_Reporting.EMPLOYEE_TYPE,
tbl_DB_Supply_Demand_Reporting.MANAGER_USER_NAME,
tbl_DB_Supply_Demand_Reporting.ePRO_STATUS,
tbl_DB_Supply_Demand_Reporting.ePRO_CATEGORY,
tbl_DB_Supply_Demand_Reporting.CLIENT_ID,
tbl_DB_Supply_Demand_Reporting.CLIENT_NAME,
tbl_DB_Supply_Demand_Reporting.[DEMAND RSC POOL],
tbl_DB_Supply_Demand_Reporting.[SUPPLY RSC POOL],
tbl_DB_Supply_Demand_Reporting.ORGANIZATION,
tbl_DB_Supply_Demand_Reporting.FUNCTION,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_ID,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_NAME,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_START_DATE,
tbl_DB_Supply_Demand_Reporting.STAFF_PROF_END_DATE,
tbl_DB_Supply_Demand_Reporting.PROJECT_ID,
tbl_DB_Supply_Demand_Reporting.PROJECT_NAME,
tbl_DB_Supply_Demand_Reporting.PROJECT_CATEGORY,
tbl_DB_Supply_Demand_Reporting.SKILL_NAME,
tbl_DB_Supply_Demand_Reporting.STAFFED_STATUS,
tbl_DB_Supply_Demand_Reporting.RESOURCE_POOL,
tbl_DB_Supply_Demand_Reporting.UNIT,
tbl_DB_Supply_Demand_Reporting.OPS_CLIENT_FOCUS_TEAM
PIVOT tbl_DB_Supply_Demand_Reporting.FTE_DATE_YM In ("Month 1","Month
2","Month 3","Month 4","Month 5","Month 6","Month 7","Month 8","Month
9","Month 10","Month 11","Month 12");


John Spencer said:
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

You need to change your PIVOT clause to generate Month 1 etc. That could be
as simple as

PIVOT "Month " & Val(Mid(FTE_Date_YM,5)) IN ("Month 1", "Month 2", ...)

or it may be a bit more complex and require you to use something more complex.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a crosstab query with 6 row heading fields and then the field
[FTE_DATE_YM] as the column heading and the field [Hours] as value which is a
sum. The FTE_DATE_YM field is a series of 12 months in this format 200801,
200802, 200803 etc. The query works fine.

The issue I am having is I want the column heading to be consistent no
matter the month date so I set query properties column heading to this:
"Month 1","Month 2","Month 3","Month 4","Month 5","Month 6","Month 7","Month
8","Month 9","Month 10","Month 11","Month 12"

When I do this the column heading appear as I want but then the [Hours]
value field which is a sum no longer shows any numbers. I do have a Total of
Hours row field which does show the correct total of hours for the months but
the individual months have no values.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top