不支持SQL Join表达式

问题描述

以下是我的查询:

SELECT
   UT.AgentName AS [Agent Name],
   UT.TeamName AS Team,
   Format(Now(),"Short Date") AS [As Of],
   Sum(I.RegPointValue) AS Points
FROM
   (SELECT
       UU.AgentID,
       (Nz(UU.LastName,'')+", "+Nz(UU.FirstName,'')) AS AgentName ,
       TT.TeamName
    FROM
       Users AS UU
      INNER JOIN
       Teams AS TT
      ON UU.TeamID = TT.TeamID) AS UT
 LEFT JOIN
    (InfractionTypes AS I
   INNER JOIN
      (DateCodes AS D
     INNER JOIN
        AquiredInfractions AS AI
     ON D.DateID = AI.DateID)
   ON I.InfractionID = AI.InfractionID)
 ON UT.AgentID = AI.AgentID
WHERE (((D.DateValue)>=#4/1/2014#))
GROUP BY UT.TeamName, UT.AgentName, I.RegPointValue;

这是根据一个人收到的出勤率总结一个人将获得的所有积分.如果将LEFT JOIN更改为INNER JOIN，则查询有效，但仅返回已收到出勤违规的人员的姓名.但是我想要的是返回所有人的名字，如果他们没有得到任何奖励，他们的分数将为0.

What this does is sum up all of the points that a person would get depending upon the attendance infractions that they have recieved. If I change the LEFT JOIN to INNER JOIN the query works but only returns the names of people that have received attendance infractions. But what I would like is for it to return the names of all people and have 0 for their points if they haven't received any.

我尝试保存或执行时遇到的错误是Join expression not supported.我一直在尝试通过弄乱联接的顺序使它在最近几个小时内正常工作，但无济于事.使用MS-Access 2013.

The error that I get when I try to save or execute is Join expression not supported. I have been trying to get this to work for the last few hours by messing with the order of the joins but to no avail. Using MS-Access 2013.

推荐答案

您缺少一些别名.

每个用作表的内部查询 都必须赋予一个别名，并且在加入它们时，必须使用该别名，并且仅使用联接条件上的选定列.

Every inner query used as a table must be given an alias, and when joining them you must use that alias and only the selected columns on the join condition.

例如，向这些行添加别名:

For example, add aliases to these lines:

...
    ON D.DateID = AI.DateID) AS SOMETHING_1
  ON I.InfractionID = AI.InfractionID) AS SOMETHING_2

等

这篇关于不支持SQL Join表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！