By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,661 Members | 781 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,661 IT Pros & Developers. It's quick & easy.

Cross tab qry for report

Fotorat
P: 13
Access 97 windows XP Pro

I have done a crosstab query for a report with 12 months as the columns and job codes as rows counting people as the value:

Link to view report so far

Ps the zeros in there so far came from an outer join so I could show Job codes with no transfers or leavers and I used rotate text active X for the headers.

I have 2 questions

1- How can I force "0" in the null values of the pivot so that my row totals in the report work? This is the crosstab (I cant seem to get IIF - IsNull to work!!):

TRANSFORM Sum(IIf([code]="L",[TOTAL],[TOTAL])) AS SumOfTOTAL
SELECT UNION.JOB
FROM [UNION]
WHERE (((UNION.JOB) Not Like "**Other - Non RetailPosition**" And (UNION.JOB) Not Like "Dec*"))
GROUP BY UNION.JOB
ORDER BY [UNION].[MONTH] & '-' & [code]
PIVOT [UNION].[MONTH] & '-' & [code];


2- How can I assign 0,1,2,3,4,5,6,7,8,9,10,11,12 to my cross tab columns so that the report detail txt field names dont change each month when I increment the report 1 month.

if suggesting code please provide examples!
Oct 26 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,470
In answer to question one - if the 'IIf(IsNull([Field]),0,[Field])' construct doesn't work try Nz([Field],0)
Oct 26 '06 #2

Fotorat
P: 13
Brilliant - many thanks

I had tried that earlier with no success but this worked:

TRANSFORM Nz(Sum(IIf([code]="L",[TOTAL],[TOTAL])),0) AS SumOfTOTAL

No if anyone can start me of one assigning 0 to 12 for the last 12 months

I am clutching at straws like this:

MonthsAgo: IIf(DatePart("m",[EFFDT])=DateAdd("m",-1,Now()),"-1","next iif")
Oct 26 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534

MonthsAgo: IIf(DatePart("m",[EFFDT])=DateAdd("m",-1,Now()),"-1","next iif")
I think you're looking for

MonthsAgo: Month(Now()) - Month([EFFDT])
Oct 27 '06 #4

PEB
Expert 100+
P: 1,418
PEB
To assign the months replace the last line with this one:

PIVOT ([UNION].[MONTH] & '-' & [code]) in (1, 2, 3, 4,5,6,7,8,9,10,11,12);
Oct 27 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.