[AccessD] Missing months in a crosstab query

Hi Susan,
If you create an OUTER JOIN rather than an INNER JOIN to show all 
records in the table that holds the months, I think you will solve your 
problem.  I can't tell you how many times I have had to rediscover the 
basic truth that INNER JOIN means I will see only those records with 
matching field entries in both tables.
HTH,
Tina

Klos, Susan wrote:

>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
>
> 
>
>  
>