SQL Outer Join contains ambiguous statement

[Jim Dudley]

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 join and then include that query in your SQL statement.

SQL Statement:

TRANSFORM Sum(tbl_Attendance.Credits) AS SumOfCredits
SELECT tbl_Attendance.SNum, tbl_Students.LName, tbl_Students.FName, tbl_Students.Campus, tbl_Students.Email, tbl_Students.FGen, tbl_CertificatesLog.Notes AS Expr1, tbl_CertificatesLog.Bronze, tbl_CertificatesLog.Silver, tbl_CertificatesLog.Gold, tbl_CertificatesLog.Platinum, Sum(tbl_Attendance.Credits) AS [Total Of Credits], Sum(tbl_WS_LearningOutcomes.LO1) AS SumOfLO1, Sum(tbl_WS_LearningOutcomes.LO2) AS SumOfLO2, Sum(tbl_WS_LearningOutcomes.LO3) AS SumOfLO3, Sum(tbl_WS_LearningOutcomes.LO4) AS SumOfLO4, Sum(tbl_WS_LearningOutcomes.LO5) AS SumOfLO5, Sum(tbl_WS_LearningOutcomes.LO6) AS SumOfLO6
FROM tbl_CertificatesLog LEFT JOIN (tbl_WS_LearningOutcomes INNER JOIN (tbl_WS INNER JOIN (tbl_Students INNER JOIN tbl_Attendance ON tbl_Students.SNUM = tbl_Attendance.SNUM) ON tbl_WS.Wcode = tbl_Attendance.WCode) ON tbl_WS_LearningOutcomes.Wcode = tbl_WS.Wcode) ON tbl_CertificatesLog.SNUM = tbl_Students.SNUM
GROUP BY tbl_Attendance.SNum, tbl_Students.LName, tbl_Students.FName, tbl_Students.Campus, tbl_Students.Email, tbl_Students.FGen, tbl_CertificatesLog.Notes, tbl_CertificatesLog.Bronze, tbl_CertificatesLog.Silver, tbl_CertificatesLog.Gold, tbl_CertificatesLog.Platinum
ORDER BY tbl_Attendance.SNum
PIVOT tbl_Attendance.Type;

System:

Access 2010, Windows 7

Issue:

I am relatively new to SQL and understand I need to create a query but what do I need to query and how do I include that query in the statement?

Any input will be greatly appreciated.

Thank you.

Jim

 

[NickHa]

The SQL parser is confused by this clause

FROM tbl_CertificatesLog _
     LEFT JOIN (tbl_WS_LearningOutcomes _
                 INNER JOIN (tbl_WS _
                            INNER JOIN (tbl_Students _
                                         INNER JOIN tbl_Attendance _
                                        ON tbl_Students.SNUM = tbl_Attendance.SNUM) _
                             ON tbl_WS.Wcode = tbl_Attendance.WCode) _
                 ON tbl_WS_LearningOutcomes.Wcode = tbl_WS.Wcode) _
      ON tbl_CertificatesLog.SNUM = tbl_Students.SNUM

- maybe where tbl_WS.Wcode appears in two separate joins(?).

I would start by resolving the innermost joins to individual queries which have a common field, then try the crosstab query using those queries as its data sources joined by the common field. The common fields appear to be SNUM and Wcode, so aim to resolve those linkages first.

The objective is to reduce your five input tables to one SELECT query, then use that query as the source for your crosstab query. If you can't get to a single query as the input, then try for two. It's hard to say without having real data to test.