Query error - ambiguous outer joins?

[totslnewbie]

Hi all,



Question related to a query I've created that draws from two different, unrelated tables:
Table 1 is the masterlist of Participants, Table 2 lists all Data types (9 in total) to be collected from each participant.

The query will calculate the start and end date of each data collection type, and I would eventually like to filter this query for each month to know which participant will require what data collection type in each month.

Another table, which is joined to both tables through a Foreign key? contains the list of all data collection events, by participant and by data type.

In the query, some of the data collection values for which the start and end dates have been calculated would have been completed already, and would be listed in Table 3 (where all the data collection events are inputted). Wherever a record of data collection for a participant occurs in Table 3, I would like the query to somehow identify that either it has been completed, or if the end date window has passed already (less than now), and there is no record in Table 3 of a data collection event, that it is a Missed Data Point.

When I try to use Table 3 in the query, it limits the records of that query only to those records that show up in Table 3, but I want it to show all participants (from Table 1) and all data points required for each participant (Table 2), and then identify completed dates where they show up in Table 3 ....

I tried to make the Join type an outer join? but got this error: The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement


here's the sql code:

SELECT tblIParticipantMasterListInfo.PARTICIPANTID, tblDataCollectionTypes.DataID, Switch([DataID]=1,[DateA1CT0]+76,[DataID]=2,[DateA1CT0]+166,[DataID]=3,[DateA1CT0]+256,[DataID]=4,[DateA1CT0]+346,[DataID]=5,[RecruitmentDate]+76,[DataID]=6,[RecruitmentDate]+166,[DataID]=7,[RecruitmentDate]+256,[DataID]=8,[RecruitmentDate]+346,[DataID]=9,[DischargeDate]+14) AS StartDate, Switch([DataID]=1,[DateA1CT0]+120,[DataID]=2,[DateA1CT0]+210,[DataID]=3,[DateA1CT0]+300,[DataID]=4,[DateA1CT0]+390,[DataID]=5,[RecruitmentDate]+120,[DataID]=6,[RecruitmentDate]+210,[DataID]=7,[RecruitmentDate]+300,[DataID]=8,[RecruitmentDate]+390,[DataID]=9,[DischargeDate]+30) AS EndDate
FROM tblIParticipantMasterListInfo INNER JOIN (tblDataCollectionTypes LEFT JOIN tDataCompletedDates ON tblDataCollectionTypes.DataID = tDataCompletedDates.[Data Collection ID]) ON tblIParticipantMasterListInfo.PARTICIPANTID = tDataCompletedDates.ParticipantID;

[orange]

You may find some helpful info here
https://support.office.com/en-gb/art...6-356c021b635b