SQL Join expression not supported
Below is my request:
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;
What this means is, add up all the points that a person will receive depending on the attendance violations they received. If I change LEFT JOIN
to INNER JOIN
, the query works, but it only returns the names of people who received attendance violations. But I would like it to return all people's names and have 0 points if they didn't get it.
The error I get when trying to save or execute is Join expression not supported
. I've been trying to get this to work for the past few hours by messing around with the order of attachments, to no avail. Using MS-Access 2013.
source to share
You are missing some aliases.
Every internal query used as a table must be assigned an alias, and when joining them you must use that alias and only the selected columns in 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
etc.
source to share