Crosstab Query Column Headings (1 Viewer)

kevisull

New member
Local time
Today, 06:53
Joined
Dec 2, 2008
Messages
5
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.
 

Kiwiman

Registered User
Local time
Today, 10:53
Joined
Apr 27, 2008
Messages
799
Howzit

When you set the properties like that in a cross tab, it will only return values where the column headings would normally come out as - in your case "Month 6, Month 7...".

Any "Column Headings that do not meet that criteria will be ignored

If you haven't formatted the columns to come like you have specified in the properties, nothing will show.

In your crosstab query you will want something like the below.

Code:
TRANSFORM Sum(Yourtable.yourvalue) AS SumOfAmount
SELECT Yourtable.yourrows
FROM Yourtable
GROUP BY Yourtable.yourrows
PIVOT IIf(Right([FTE_DATE_YM],2)=11 Or Right([FTE_DATE_YM],2)=12,"Month " & Right([FTE_DATE_YM],2),"Month " & Right([FTE_DATE_YM],1)) 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");
 

kevisull

New member
Local time
Today, 06:53
Joined
Dec 2, 2008
Messages
5
I do not understand code. Should I add something to the query properties column headings?
 

Kiwiman

Registered User
Local time
Today, 10:53
Joined
Apr 27, 2008
Messages
799
Howzit

Hi my code below is the SQL view of a query - to see this on your tool bar select View >> SQL View.

As I see it:

  • You hve changed the column headings properties to your desired output
  • You may not however have formatted the field to be the same as your desired output
    • In the design view of your query:
      • Select the column heading field
      • I expect this will have FTE_DATE_YM in your case
      • it will need to show (or something like it) IIf(Right([FTE_DATE_YM],2)=11 Or Right([FTE_DATE_YM],2)=12,"Month " & Right([FTE_DATE_YM],2),"Month " & Right([FTE_DATE_YM],1))
      • It is bullet 3 above that formats the field to your desired output, therefore the crosstab will pick up the results
 

kevisull

New member
Local time
Today, 06:53
Joined
Dec 2, 2008
Messages
5
So I am still a little confused on the Iff statement in the Column Heading property. I know where the statement should go but can you help me understand how to write it.

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.

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 so on...

So how do I write this Iff statement knowing [FTE_DATE_YM] will be dynamic and always changing but need to match the Month # format and allow my Hours field to show the sums.

Thank you for all the quick responses thus far I am excited to find a solution here.
 

kevisull

New member
Local time
Today, 06:53
Joined
Dec 2, 2008
Messages
5
here is the current code if you can adjust it to work i can paste it in but that is my extent of doing code :(

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");
 

Kiwiman

Registered User
Local time
Today, 10:53
Joined
Apr 27, 2008
Messages
799
Howzit

A little more complicated your last post, as the Month 1 could now represent actual month 8 or 9, depending on when you run the report. My initial response assumed that months 01 would always fall in month 01


See an example in the attached db.

I have included a module to change the months based on a "month" you select to run the query,

The module is incomplete (i have only filled in months 1,7 and 12)- you will neeed to fill in the rest of the months. Just follow the same format that is there, and work out the logic of how many to add\subtract to which months depending on when the report is run

It is the best i can do - it should work. Some others may have ideas on how to improve the module - as my vb skills aren't as good as others on this forum.

Open the form, select a month and run the report.

Both queries call a Public function called crsstb and passes it:

  • the "Month" you run the report
  • the actual month of the record

The function should return which crosstab month you want the record to show - Month 1, month 2, month 3... (which i have specified as the column Headings in the Query Properties - Column Headings

Have a look at the design view of the query to see how it works
 

Attachments

  • Crosstab2.zip
    33.3 KB · Views: 246

kevisull

New member
Local time
Today, 06:53
Joined
Dec 2, 2008
Messages
5
ok diff question.

I have the below query and everytime i change it to Ascending in the design view not the code view Access encounters an error and must close. can you help me rewrite the code so it is ascending rather than descending.

SELECT Q.FTE_DATE_YM, (SELECT COUNT(*) FROM [qry_AvailableReportingMonths] Q1
WHERE Q1.FTE_DATE_YM > Q.FTE_DATE_YM)+1 AS Rank, "Month_0" & [Rank] AS Format1, Left([Format1],6) & Right([Format1],2) AS [Month]
FROM qry_AvailableReportingMonths AS Q
ORDER BY Q.FTE_DATE_YM DESC;
 

Kiwiman

Registered User
Local time
Today, 10:53
Joined
Apr 27, 2008
Messages
799
Howzit

Does this work??

Code:
SELECT Q.FTE_DATE_YM, (SELECT COUNT(*) FROM [qry_AvailableReportingMonths] Q1
WHERE Q1.FTE_DATE_YM > Q.FTE_DATE_YM)+1 AS Rank, "Month_0" & [Rank] AS Format1, Left([Format1],6) & Right([Format1],2) AS [Month]
FROM qry_AvailableReportingMonths AS Q
ORDER BY Q.FTE_DATE_YM [COLOR="Red"][B]ASC[/B][/COLOR];
 

Users who are viewing this thread

Top Bottom