Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 12 votes, 5.00 average. Display Modes
Old 12-02-2008, 01:47 PM   #1
kevisull
Registered User
 
Join Date: Dec 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
kevisull is on a distinguished road
Crosstab Query Column Headings

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.

kevisull is offline   Reply With Quote
Old 12-02-2008, 03:40 PM   #2
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 56 Times in 56 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: Crosstab Query Column Headings

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");
__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Old 12-02-2008, 04:31 PM   #3
kevisull
Registered User
 
Join Date: Dec 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
kevisull is on a distinguished road
Re: Crosstab Query Column Headings

I do not understand code. Should I add something to the query properties column headings?

kevisull is offline   Reply With Quote
Old 12-02-2008, 11:42 PM   #4
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 56 Times in 56 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: Crosstab Query Column Headings

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
__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Old 12-03-2008, 04:24 AM   #5
kevisull
Registered User
 
Join Date: Dec 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
kevisull is on a distinguished road
Re: Crosstab Query Column Headings

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 is offline   Reply With Quote
Old 12-03-2008, 05:23 AM   #6
kevisull
Registered User
 
Join Date: Dec 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
kevisull is on a distinguished road
Re: Crosstab Query Column Headings

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_DA TE, 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_TE AM, 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_DA TE, 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_TE AM
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");
kevisull is offline   Reply With Quote
Old 12-03-2008, 06:05 AM   #7
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 56 Times in 56 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: Crosstab Query Column Headings

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
Attached Files
File Type: zip Crosstab2.zip (33.3 KB, 163 views)

__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Old 12-03-2008, 07:55 AM   #8
kevisull
Registered User
 
Join Date: Dec 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
kevisull is on a distinguished road
Re: Crosstab Query Column Headings

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;
kevisull is offline   Reply With Quote
Old 12-03-2008, 08:08 AM   #9
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 56 Times in 56 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: Crosstab Query Column Headings

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 ASC;

__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Column Headings Crosstab Report natep26 Queries 6 09-08-2008 11:44 PM
column header error in crosstab query TLJ Queries 1 01-23-2005 09:51 AM
Crosstable query returns too many records Palsam Queries 2 03-09-2004 11:41 AM
Problem with a report based on a crosstab query AshikHusein Reports 4 07-02-2003 04:55 AM
Crosstab Column Sort Dugantrain Queries 1 02-03-2003 08:21 PM




All times are GMT -8. The time now is 04:38 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World