加入两个查询在Access

Question

我有四个表：

客户

ID | CLIENT_NUM | Year 

农场

ID | ClientID | FARM_NUM 

场

ID | FarmID | FIELD_NUM | RANK 

SoilSheet

ID | FarmID | FieldID | SAMP_NUM | Year 

我需要从Rank，FIELD_NUM和Year排序的Soilsheet中获取数据。所以，我试过这个：

SELECT FL.Rank,FL.FIELD_NUM, S.Year, SAMP_NUM 
FROM (((SoilSheet S 
INNER JOIN Farms F ON F.ID = S.FarmID) 
INNER JOIN Clients C ON C.ID = F.ClientID) 
INNER JOIN Fields FL ON FL.ID = S.FieldID) 
WHERE C.CLIENT_NUM = 1 AND F.FARM_NUM = 1 
ORDER BY C.CLIENT_NUM, F.FARM_NUM, FL.Rank, FL.FIELD_NUM, S.Year 

问题是RANK可能会在每年不同，所以它没有正确排序。例如，我需要按2015年的RANK排序。

通过上述，我得到这样的结果：

RANK | FIELD_NUM | Year | SAMP_NUM 
    0 |  19-2 | 2015 | 3000 
    2 |  20-1 | 2015 | 3001 
    2 |  20-1 | 2014 | 2002 
    2 |  20-1 | 2015 | 1003 
    20 |  19-2 | 2014 | 2001 

19-2是RANK 0在2015年，但20在2014年因此，我尝试这样做：

SELECT FL.Rank,FL.FIELD_NUM, S.Year, SAMP_NUM 
FROM ((Clients C 
INNER JOIN Farms F ON F.ClientID = C.ID) 
INNER JOIN Fields FL ON FL.FarmID = F.ID) 
LEFT JOIN (((SoilSheet S 
INNER JOIN Fields FLS ON FLS.ID = S.FieldID) 
INNER JOIN Farms FS ON FS.ID = S.FarmID) 
INNER JOIN Clients CS ON CS.ID = FS.ClientID) 
ON 
(FLS.FIELD_NUM = FL.FIELD_NUM 
AND FS.FARM_NUM = F.FARM_NUM 
AND CS.CLIENT_NUM = C.CLIENT_NUM) 
WHERE C.CLIENT_NUM = 1 AND F.FARM_NUM = 1 AND C.Year = 2015 
ORDER BY C.CLIENT_NUM, F.FARM_NUM, FL.Rank, FL.FIELD_NUM, S.Year 

这工作正常SQL Server：

RANK | FIELD_NUM | Year | SAMP_NUM 
    0 |  19-2 | 2015 | 3000 
    0 |  19-2 | 2014 | 2001 
    2 |  20-1 | 2015 | 3001 
    2 |  20-1 | 2014 | 2002 
    2 |  20-1 | 2015 | 1003 

在Access中，它给出'Join expression not supported。'。我猜这是因为Access不喜欢加入INNER连接，但我只是猜测。

任何想法？订购或另一种方式来加入另一种方式？

Answer 1

您是否试图将TSQL从SQL Server复制到Access？不幸的是，如果不修改Joins，这将无法正常工作。具体来说，您需要在连接子句的周围放置括号。

退房此了解更多信息：

SQL Inner Joins with multiple tables

Answer 2

我终于得到它的工作

SELECT C1.Year,C1.RANK, C1.FIELD_NUM, C.CLIENT_NUM, F.FARM_NUM FROM 
(SELECT C.Year,FL.RANK, FL.FIELD_NUM, C.CLIENT_NUM, F.FARM_NUM FROM 
((Clients C 
INNER JOIN Farms F ON F.ClientID = C.ID) 
INNER JOIN Fields FL ON FL.FarmID = F.ID)) C1 
LEFT JOIN 
(SELECT S.Year, SAMP_NUM,FLS.FIELD_NUM, FS.FARM_NUM, CS.CLIENT_NUM 
FROM ((SoilSheet S 
INNER JOIN Fields FLS ON FLS.ID = S.FieldID) 
INNER JOIN Farms FS ON FS.ID = S.FarmID) 
INNER JOIN Clients CS ON CS.ID = FS.ClientID) S1 
ON (S1.FIELD_NUM = C1.FIELD_NUM AND S1.FARM_NUM = C1.FARM_NUM 
AND S1.CLIENT_NUM = C1.CLIENT_NUM) 
WHERE C.CLIENT_NUM = 1 AND F.FARM_NUM = 1 AND C.Year = 2015 
ORDER BY FL.Rank, FL.FIELD_NUM