[AccessD] Missing months in a crosstab query

Didn't you ask about this a couiple of weeks ago? Did you try this?

"Check out p 157 of Getz's ADH. He suggests using the IN clause. EX:
PIVOT column-headings-field [IN(value1[,value2[,...etc]])]"

Jim Hale

-----Original Message-----
From: Klos, Susan [mailto:Susan.Klos at fldoe.org]
Sent: Monday, November 08, 2004 9:39 AM
To: 'AccessD at databaseadvisors.com'
Subject: [AccessD] Missing months in a crosstab query


I have the following crosstab query which I am using to bind to a report:

TRANSFORM Sum(tlnklodgingEvent.Guests) AS [Number]

SELECT IIf([weekend]=True,"Cabin occupancy/Weekend","Cabin
occupancy/Midweek") AS Type, "Cabin" AS Category

FROM tblEvent INNER JOIN tlnklodgingEvent ON tblEvent.InvoiceNo =
tlnklodgingEvent.EventID

WHERE (((tlnklodgingEvent.LodgingID)=5 Or (tlnklodgingEvent.LodgingID)=7 Or
(tlnklodgingEvent.LodgingID)=27 Or (tlnklodgingEvent.LodgingID)=30))

GROUP BY IIf([weekend]=True,"Cabin occupancy/Weekend","Cabin
occupancy/Midweek"), "Cabin"

PIVOT Month(tblEvent.Beginningdate);

 

I want to have the report show all months even though there may be no data
for a particular month.  In the above query, I get data for January through
June and September through December.  Nobody stayed in cabins during July
and August.  So in the report I want to show zeroes in those columns.  I
tried using text boxes and IIF statements to test for null but I knew that
wouldn't work because month numbers 7 and 8 aren't null they just don't
exist in the query.  I tried looking in help to see if there was an "if
exists" function to see if I could see if the field exists in the query.  I
couldn't find anything like that.  Should I be working at the query level
instead to have the query insert a zero if there are months missing?  If so
I will need help on that also.  Thanks in advance for any help.

 

Susan Klos

Senior Database Analyst

Evaluation and Reporting

Florida Department of Education

850-245-0708 

sc 205-0708

 

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.