INNER JOIN and WHERE clause problems

[kman42]

I have two tables that I need to join for a report as follows:

tbl_Initiatives


-------------
pk_InitiativeID
Title


tbl_EventLog
------------
pk_EventLogID
fk_InitiativeID
fk_EventID
DateOfEvent

tbl_EventLog contains multiple entries for each tbl_Initiatives record and I need the output to have columns for the DateOfEvent corresponding to each fk_EventID in tbl_EventLog. So I created the following query:

SELECT tbl_Initiatives.Title, EL1.DateOfEvent, EL2.DateOfEvent, EL3.DateOfEvent
FROM tbl_Initiatives INNER JOIN ((tbl_EventLog EL1 ON tbl_Initiatives.pk_InitiativeID=EL1.fk_InitiativeID) INNER JOIN (tbl_EventLog EL2 ON tbl_Initiatives.pk_InitiativeID=EL2.fk_InitiativeID) INNER JOIN tbl_EventLog EL3 ON tbl_Initiatives.pk_InitiativeID=EL3.fk_InitiativeID WHERE EL1.fk_EventID=1 AND EL2.fk_EventID=2 AND EL3.fk_EventID=3

This obviously doesn't work as it won't return the rows that have null values for any of the dates, even though I want all of the initiatives even if they have no dates or only some dates. I tried "ON tbl_Initiatives.pk_InitiativeID=EL1.fk_InitiativeI D AND EL1.fk_EventID=1" but that isn't supported by Access. Putting "OR EL1.fk_EventID is null" in the WHERE clause gives me a gazillion records.

And I can't use subqueries because Access can't run the report since it is grouped. I was originally using Dlookup in the query and in the report, but it is really slow.

Is there any way out of this mess?

Thanks,
kman

[TheShabz]

Why are you pulling the event date so many times? Are you trying to get something that takes:
pk_EventLogID | fk_InitiativeID | fk_EventID | DateOfEvent
1 | A | 4 | 1/1/2011
2 | A | 5 | 1/2/2011
3 | A | 6 | 1/3/2011

and turn it into:
A | 1/1/2011 | 1/2/2011 | 1/3/2011
?

If so you need to use a crosstab query.

[kman42]

Why are you pulling the event date so many times? Are you trying to get something that takes:
pk_EventLogID | fk_InitiativeID | fk_EventID | DateOfEvent
1 | A | 4 | 1/1/2011
2 | A | 5 | 1/2/2011
3 | A | 6 | 1/3/2011

and turn it into:
A | 1/1/2011 | 1/2/2011 | 1/3/2011
?

If so you need to use a crosstab query.

Because I'm a newb and crosstab queries weren't on my radar. It sounds like the right approach though, so I'll look into it. Thanks for the helpful reply.

kman