Including All filed Items As Column Headers In Crosstab Query 2

[ooch1]

Hello,

I'm trying to build a crosstab query that counts the number of proposals and groups them by start date.

I then hope to union this with similar count queries, that relate to different fields.

However, it is only including the months where there is associated info, where as i would like to include the months where nothing has been received (i.e. Every month over 2 years).

I've tried the nz on the proposals, but this only puts 0's into the months that are already icluded. I have also tried typing in the months into the properties window (Jan04, Feb04....), this only confused matters as it could not relate to the text (presumably?).

My SQL as it stands is:

TRANSFORM nz(Count([Proposal Number]),0) AS Expr1
SELECT "Proposals" AS Proposal, SBFs.[New/Renewal]
FROM SBFs, Celendar
WHERE (((nz([Start Date])) Between [Celendar]![Month Start date] And [Celendar]![Month end date]) AND ((SBFs.[Sales Channel Description]) Like "*conn*"))
GROUP BY "Proposals", SBFs.[New/Renewal]
ORDER BY Celendar.Month
PIVOT Celendar.Month;

Cheers

OOch

 

[ZmrAbdulla]

When you assign month value to the column heading in the property, you should be careful about the formating of the month display.
For

FldMonth: Format([fldDate],[COLOR=red]"mmm"[/color])

aplly this

"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

for

FldMonth: Format([fldDate],[COLOR=red]"mmmm"[/color])

aplly this

"January","February","March","April".......

for

FldMonth: Format([fldDate],[COLOR=red]"mmmyy"[/color])

aplly this

"Jan04","Feb04","Mar04","Apr04".......

hope this helps

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[PHV]

You may try this:
PIVOT Celendar.Month IN ('Jan04','Feb04',...,'Dec04');

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

 

[ooch1]

Cheers guys, both elements have done the trick!!

OOch