MS Access "JOIN expression not supported"

Question

Why is my query not being accepted by Access?

SELECT
    NCR.Stage
    , AG.ID AS AgeGroup
    , Count(*) AS AgeGroupCount
FROM (
    qry1 AG
    LEFT JOIN qry1 AGi ON (
        AG.ID = AGi.ID + 1
    )
)
LEFT JOIN qry2 AS NCR ON (
    AG.AgeGroupLowerLimit <= NCR.Age
    AND (
        AGi.ID IS NULL
        OR AGi.AgeGroupLowerLimit > NCR.Age
    )
)
GROUP BY
    NCR.Stage
    , NCR.AgeGroup
;

The problem is related to the second left join, with qry2.

Answer 1

I think, expression

(  AGi.ID IS NULL
        OR AGi.AgeGroupLowerLimit > NCR.Age
    )

you are referring to a field that is not available for reference in another table (part of the query). You can try to move this condition to WHERE clause.

Try this

SELECT
    NCR.Stage
    , AG.ID AS AgeGroup
    , Count(*) AS AgeGroupCount
FROM (
    qry1 AG
    LEFT JOIN qry1 AGi ON AG.ID = (AGi.ID + 1)
)
LEFT JOIN qry2 AS NCR ON AG.AgeGroupLowerLimit <= NCR.Age
WHERE (
        AGi.ID IS NULL
        OR AGi.AgeGroupLowerLimit > NCR.Age
    )
GROUP BY
    NCR.Stage
    , NCR.AgeGroup
;

Also, in SELECT clause AG.ID AS AgeGroup - mast be part of GROUP BY.