Tricky joins / nulls

[oneannoyingguy]

I am having some trouble reaching my intended results in combining information from a few tables. The easiest way to explain is with a dummy model

t1________________________
date - portfolio - group - contn
======================
d1 A a 5
d1 A b 6
d1 A c 7
d2 A b 9
d2 A c 10
d2 A d 11

t2____________
availablegroups
===========
a
b
c
d
e

My objective is to get to either a query or table with
_______________________________
date - portfolio - group - contn
======================
d1 A a 5
d1 A b 6
d1 A c 7
d1 A d 0 OR Null
d1 A e 0 OR Null
d2 A a 0 OR Null
d2 A b 9
d2 A c 10
d2 A d 11
d2 A e 0 OR Null

I've tried using t2 left join t1, but I end up losing the date and portfolio entries for groups that are available for use, but not used in the portfolio.
Bottom line -- I need a contribution number for every group that was available for every date available (the groups available are always the same), regardless of if there are transactions for that group
Any help?

~Chris

[NeoPa]

Not at all straightforward (some fun though), but I knocked up a quick test (which seems to work as required).
tblCont:
Date Group Contn
D1 A 5
D1 C 6
D1 E 7
D2 A 10
D2 B 11
D2 C 12

tblGroup:
Group
A
B
C
D
E

SELECT subAll.Date, subAll.Group, Nz([tblCont].[Contn],[subAll].[Contn]) AS Contn
FROM (SELECT [Date], [Group], 0 AS Contn
FROM tblGroup, (SELECT DISTINCT [Date]
FROM tblCont) AS subDate
ORDER BY [Date], [Group]) AS subAll LEFT JOIN tblCont ON (subAll.Date = tblCont.Date) AND (subAll.Group = tblCont.Group);

Produces :-

Date Group Contn
D1 A 5
D1 B 0
D1 C 6
D1 D 0
D1 E 7
D2 A 10
D2 B 11
D2 C 12
D2 D 0
D2 E 0

NB. Uses two levels of subquery. This is not an accident & I don't think it can be done (to work correctly) with anything less.