12 Rolling Crosstab with less than 12 monts data 2

[kentwoodjean]

Have 12 month rolling crosstabs for 4 departments that work great as they have about 15 months data. A 5th department department began in July so there is not 12 months data. Is there anyway to format the report with the 12 months but perhaps show a 0 or leave blank until such time as there is data to fill the cells? Below is my SQL:

TRANSFORM Sum([92670ScorecardUnionName].Hrs) AS SumOfHrs
SELECT [92670ScorecardUnionName].EMPID, [92670ScorecardUnionName].Category, [92670ScorecardUnionName].NAME, [92670ScorecardUnionName].Description, Avg([92670ScorecardUnionName].Hrs) AS [Total Of Hrs]
FROM 92670ScorecardUnionName
GROUP BY [92670ScorecardUnionName].EMPID, [92670ScorecardUnionName].Category, [92670ScorecardUnionName].NAME, [92670ScorecardUnionName].Description
PIVOT Format([Date],"mmm");

 

[PHV]

You may try something like this:
PIVOT Format([Date],"mmm") IN ('Jan','Feb', ..., 'Dec');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kentwoodjean]

Excellent!! Does exactly what I want it too. I knew there had to be a way but I just couldn't get there.

While we are on crosstabs, I have another situation that is similar but different.

Crosstab set up by Month with 1-31 as days for column headings. I set it up in Mid 2005 and showed 2005 as criteria and all work days or overtime days were populated under the correct day #. For 2006, I simply changed the criteria year to 2006 but now I am getting errors because it does not recognize the non-work days of 1 or 2 (1/1 & 1/2). What is the difference between using 2005 to 2006? My reason setting the reporting up this way was so that I would not have to recreat it each year. It does not role from one year to another but instead represents the current year results. Here is the SQL. Any thoughts?

TRANSFORM Sum(RC_ProfRec.CountOfDate1) AS SumOfCountOfDate1
SELECT RC_ProfRec.RespDefID, RC_ProfRec.RespCde, Month([Date1]) AS TheMonth, Sum(RC_ProfRec.CountOfDate1) AS [Total Of CountOfDate1]
FROM RC_ProfRec
GROUP BY RC_ProfRec.RespDefID, RC_ProfRec.RespCde, Month([Date1])
PIVOT Format(Day([Date1]),"00");

 

[dhookom]

If you are creating crosstab reports with rolling months, you might want to check this out faq703-5466.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Read the FAQ. Would this 2nd crosstab really be considered rolling? With my 2005 results, I had 12 pages in the report, 1 page for each month with each page having column headings 1-31. There are several rows on each page as well. So, as I look at Jan 2006, I don't want to see last years numbers for January, and there would be no additional pages for months that have not occured as yet. Hope that makes sense.

If I have to go rolling, can I at least make it so January is a clean sheet except for current 2006 data and then let the other sheets be 2005 until I get there?

 

[dhookom]

I saw "PIVOT Format([Date],"mmm");" in your first sql so I thought you had months as column headings and not days. I thought your column headings were rolling as opposed to your row headings.

Regarding your second crosstab, I would think you would apply the knowledge you gained from PH and use:

...
PIVOT Format(Day([Date1]),"00") IN ("01","02","03",..."31");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

I did try that, but on 1st pass it did not work. Perhaps there was some kind of typo so I will try again at work and see what the outcome is.

 

[kentwoodjean]

Well, on 2nd pass it did work so there must have been perhaps a spacing problem. Acually, to make it work correctly, I ommitted the " around the # (01,02,03,etc) and it works perfectly. Thanks to both PHV and Duane.