How to sum all values for a group (even those not in the group) in Access SQL? -

i have table this:

mun  | type  | length -----|-------|-------- 001  |     | 5 001  |     | 2 002  |     | 3 002  | c     | 8 003  | b     | 6 

and want show each mun types , lenghts this:

mun  | type  | length -----|-------|-------- 001  |     | 7 001  | b     | 0 001  | c     | 0 002  |     | 3 002  | b     | 0 002  | c     | 8 003  |     | 0 003  | b     | 6 003  | c     | 0 

note that, example, mun 001 doesn´t have type b , c, should appear 0 length.

i've tried this:

select distinct     a.mun,     a.type     sum(a.length)     my_table     left join (select distinct type my_table) b on a.type = b,type group     a.mun, a.type 

but should more full join? i've tried make union of left join , right join, no luck.

from beginning, feel query bit nonsense , don't have in mind other way achieve this.

finally, i've managed make ugly query job. union of query each type, in case there few , it's ok, hundreds or thousands not valid answer, not accepted.

here "masterpiece":

select distinct     mun,     "a" type,     (select nz(sum(length), 0) my_table mun = t.mun , type= 'a') length my_table t union select distinct     mun,     "b" type,     (select nz(sum(length), 0) my_table mun = t.mun , type= 'b') length my_table t union select distinct     mun,     "c" type,     (select nz(sum(length), 0) my_table mun = t.mun , type= 'c') length my_table t