Joe Hecht
jmhla at earthlink.net
Wed Nov 10 21:18:59 CST 2004
Susan, I would have to find my sample but there is a way to force to hard code the 12 months in order for your cross tab. E mail me off list if you need me to find my sample for you. JOE HECHT LOS ANGELES CA jmhla at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Klos, Susan Sent: Monday, November 08, 2004 7: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