Code:
SELECT [%$##@_Alias].InitiativeID, [%$##@_Alias].EventLocation, [%$##@_Alias].fk_EventID, [%$##@_Alias].EventPhase, [%$##@_Alias].DateOfEvent, [%$##@_Alias].ElapsedDays, DLookUp("DateOfEvent","tbl_EventLog","fk_EventID=1 AND fk_InitiativeID=" & [InitiativeID]) AS DateOfPublication
FROM tbl_Initiatives INNER JOIN (SELECT fk_EventID, DateOfEvent, IIF(DateOfEvent<IIF(Nz(DLookUp("DateOfEvent","tbl_EventLog","fk_EventID=4 AND fk_InitiativeID=" & fk_InitiativeID),"")<>"",DLookUp("DateOfEvent","tbl_EventLog","fk_EventID=4 AND fk_InitiativeID=" & fk_InitiativeID),DLookup("Submit_To_DERA_Proposed","tbl_Initiatives","pk_InitiativeID=" & fk_InitiativeID)),"Phase I",IIF(DateOfEvent>=Nz(DLookUp("DateOfEvent","tbl_EventLog","fk_EventID=3 AND fk_InitiativeID=" & fk_InitiativeID),Now()),"Phase III","Phase II")) AS EventPhase, DLookUp('Location','tbl_Events','pk_EventID=' & tbl_EventLog.fk_EventID) AS EventLocation, (SELECT TOP 1 Dupe.DateOfEvent FROM tbl_EventLog AS Dupe WHERE (((Dupe.DateOfEvent)>[tbl_EventLog].[DateOfEvent]) AND (Nz(DLookup('Location','tbl_Events','pk_EventID=' & Dupe.fk_EventID),"")<>"" OR fk_EventID=1) AND ((Dupe.fk_InitiativeID)=[tbl_EventLog].[fk_InitiativeID])) ORDER BY Dupe.DateOfEvent, Dupe.pk_EventLogID) AS NextDate, IIf(Nz([NextDate],"")<>"",[NextDate]-[DateOfEvent],Now()-[DateOfEvent]) AS ElapsedDays, tbl_EventLog.fk_InitiativeID AS InitiativeID FROM tbl_EventLog WHERE (((Nz(DLookUp('Location','tbl_Events','pk_EventID=' & tbl_EventLog.fk_EventID),''))<>'')) And tbl_EventLog.fk_EventID<>1) AS [%$##@_Alias] ON tbl_Initiatives.pk_InitiativeID=[%$##@_Alias].InitiativeID
ORDER BY [%$##@_Alias].InitiativeID, [%$##@_Alias].DateOfEvent;