CODEWITHSUNDEEP
sql-serverms-access
Joe Oliphant
Joe Oliphant

Reputation: 79

Join two queries in Access

I have four tables:

Clients

ID | CLIENT_NUM | Year

Farms

ID | ClientID | FARM_NUM

Fields

ID | FarmID | FIELD_NUM | RANK

SoilSheet

ID | FarmID | FieldID | SAMP_NUM | Year

I need to get data from Soilsheet ordered by Rank, FIELD_NUM, and Year. So, I tried this:

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

The problem is RANK can be different in every year so it doesn't order properly. I need to have it order by RANK of the current year, 2015 for instance.

With the above, I get results like:

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 is RANK 0 in 2015 but 20 in 2014. So, I tried this:

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

This works fine in 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

In Access, it gives 'Join expression not supported.'. I'm guessing it's because Access doesn't like joining on INNER joins but I'm just guessing.

Any Ideas? Another way to order or another way to join?

Upvotes: 0

Views: 1418

Answers (2)

Joe Oliphant
Joe Oliphant

Reputation: 79

I finally got it to work

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

Upvotes: 1

Josh Miller
Josh Miller

Reputation: 620

Are you trying to copy the TSQL from SQL Server into Access? Unfortunately that won't work without modification to the Joins. Specifically, you will need to put parentheses around the join clauses.

Check out this for more information:

SQL Inner Joins with multiple tables

Upvotes: 0

Related Questions