Tough PIVOT problem

[ripon via AccessMonster.com]

Hi There:
I would request for a solution of this problem. I have a Select query that
uses PIVOT to show data vertically.
I always need to show all PROVCODE data in columns. Even If I dont have data,
I need to put a zero under that PROVCODE.
My code:
Transform Sum(ovs_tot - ovs_land) as om
Select tab1.year, sum(om) Alltot
from tab1inner join tab2 on tab1.month=tab2.month
where((tab1.year=tab2.year) or (tab1.year=tab2.year-1))
group by tab1.year
PIVOT tab1.provcode

I want the data to be this way always:
YEAR----Alltot-------10-----11------12------20------30
2005-----300---------50-----10-----100----40-----100
2006-----400---------50----100----60------40-----150
But when there is no data for a certain provcode(say 11), the query does not
show anyhting under that.
It actually shows data without PROVCODE 11.

YEAR----Alltot-------10------12------20------30
2005-----200---------50-----10-----100-----40
2006-----250---------50----100-----60------40

I want to show PROVCODE 11 and a zero under that:

YEAR----Alltot-------10------11---12------20------30
2005-----200---------50-----0-----10-----100-----40
2006-----250---------50-----0-----100-----60-----40

Thanks

 

[Guest]

Edit last line like this --
PIVOT tab1.provcode IN("10", "11","12","20","30");