|
| |
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
 |
Crosstab partly sorted - 7/23/2004 10:59:03
Ok, my crosstab issue is partly sorted, where by I've got the DRW to generate the layout, and then I'm messing with the code after.
Problem
If I run my query to show records in a certain year, the crosstab will only generate columns for the months it has records for. So then when the page is generated, I get an error as certain month fields don't exist.
What I therefore need to do is either get the crosstab to generate all 12 monthly fields regardless of data, or put a few if/then/else's in the handle missing fields.
Help with either method would be most welcome.
My current SQL is :
TRANSFORM round(100/Count([tblJobs.DefId])*Sum(Right(DateDiff('d',[Timestamp],[DateClosed])-(DateDiff('ww',[Timestamp],[DateClosed])*2)<=[tbldefstd].[defstd],1)),1) AS OnTime
SELECT tblUsers.OrgName
FROM tblUsers INNER JOIN (tblDefStd INNER JOIN ((tblDefCatType INNER JOIN tblDefect ON tblDefCatType.DCTId = tblDefect.DCTId) INNER JOIN tblJobs ON tblDefect.DefID = tblJobs.DefId) ON tblDefStd.DefStdId = tblDefCatType.DefStdId) ON tblUsers.UId = tblJobs.UId
WHERE (((DatePart('yyyy',[Timestamp]))=2004))
GROUP BY tblUsers.OrgName
PIVOT DatePart('m',[Timestamp]);
|
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
