无法在MS Access中使用多个子集运行查询

Question

我已将Access数据库连接到MSSQL数据库。

下面的查询在Server Management Studio(SSMS(中运行良好，但当我尝试在MS Access中运行它时，我会收到一条错误消息。

错误：查询表达式'mainSet.FT_NR=subSet.FT_NR INNER JOIN中存在语法错误(缺少运算符(。。。

有人知道我如何在MS Access中获得具有两个或多个子查询的查询吗？

我知道下面的查询可以简化。目标是让更多的子集发挥作用😉

SELECT DISTINCT mainSet.FT_NR
FROM
((SELECT FT_NR FROM tblTestdataH WHERE MILJO_ID=1) AS mainSet
INNER JOIN (SELECT FT_NR FROM tblTestdataH WHERE SYSTEM_ID=4) AS subSet ON mainSet.FT_NR = subSet.FT_NR
INNER JOIN (SELECT FT_NR FROM tblTestdataH WHERE SYSTEM_ID=1) AS subSet2 ON mainSet.FT_NR = subSet2.FT_NR)
ORDER BY mainSet.FT_NR

Answer 1

SELECT DISTINCT mainSet.FT_NR 
FROM 
((((SELECT FT_NR FROM tblTestdataH WHERE MILJO_ID=1) AS mainSet) 
INNER JOIN (SELECT FT_NR FROM tblTestdataH WHERE SYSTEM_ID=4) AS subSet ON subSet.FT_NR = mainSet.FT_NR) 
INNER JOIN (SELECT FT_NR FROM tblTestdataH WHERE SYSTEM_ID=1) AS subSet2 ON subSet2.FT_NR = mainSet.FT_NR)
ORDER BY mainSet.FT_NR