Crosstab queries

[Baard Dahl]

Hello,

I have a table that consists of three columns: Customer
number, revenue and period (January through December).
From this I have made a crosstab query with Customer
number downwards on the y axis, period horizontally on the
x axis, and the revenue asin the appropriate cells in the
junction between the two (English is not my first
language, so I hope I am explaining this properly).

My problem is that I want to base a report on this query,
but in my report I have 12 period fields, but the query
doesnt always have data for all 12 months. If I only have
data through July, for example, the report generator asks
for values for the months August through december.

Is there a way to make the query add the missing fields
without any values, or should I go about this in a
different way?

The SQL for my crosstab query looks like this:

TRANSFORM Sum(revenue) AS Sumofrevenue
SELECT Customernumber
FROM Revenueprmonth
GROUP BY Customernumber
PIVOT Period

 

[Joan Wild]

Try

TRANSFORM Sum(revenue) AS Sumofrevenue
SELECT Customernumber
FROM Revenueprmonth
GROUP BY Customernumber
PIVOT Format([Period],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");