display 12 months on a crosstab query? (1 Viewer)

[k0r54]

Ok,

I have a table with 3 fields Line address, Availability, Relevance Date
Every month i put in the data given and i have back from june (so i dont actually have 12 months.

At the moment i am grouping by line address(Row header) and datepart("m",[relevance date]) - (column header) and have the availability as a value and sum. Because i only have data from june it only gives me 6,7,8,9,10. How do i get it to always display 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and just put the availability into the relevant months and put nothin in the others.

Current SQL: -

TRANSFORM Sum(Monthly_cleansed_data.Availability) AS SumOfAvailability
SELECT Monthly_cleansed_data.[Line Address] AS LineID
FROM Monthly_cleansed_data
GROUP BY Monthly_cleansed_data.[Line Address]
ORDER BY Monthly_cleansed_data.[Line Address]
PIVOT DatePart("m",[Relevance Date]);

Thanks
k0r54

 

[Jon K]

TRANSFORM Sum(Monthly_cleansed_data.Availability) AS SumOfAvailability
SELECT Monthly_cleansed_data.[Line Address] AS LineID
FROM Monthly_cleansed_data
GROUP BY Monthly_cleansed_data.[Line Address]
ORDER BY Monthly_cleansed_data.[Line Address]
PIVOT DatePart("m",[Relevance Date]) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
.

 

[k0r54]

simple

Thanks Jon K
k0r54