Join two queries in Access

Question

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?

I'd suggest to read this: https://msdn.microsoft.com/en-us/library/bb243855%28v=office.12%29.aspx — Maciej Los, Apr 02 '15 at 21:04
From that article, "OUTER JOINs can be nested inside INNER JOINs in a multi-table join, but INNER JOINs cannot be nested inside OUTER JOINs." suggests that I can't have any inner joins in the LEFT JOIN so now what? — Joe Oliphant, Apr 02 '15 at 22:29
I suggest you to join table by table to find out when the problem begins. MS Access database engine likes parentheses. On the first look, the number of parentheses in your query is too small. — Maciej Los, Apr 03 '15 at 05:39

score 1 · Answer 1 · answered Apr 03 '15 at 16:50

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

In the end what did you change to get it to work? was it placement of the parens? — Josh Miller, Apr 14 '15 at 17:58

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

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

edited May 23 '17 at 12:28

Community

1
1

answered Apr 02 '15 at 21:03

Josh Miller

620
3
11

There are parentheses around them. It's the ON (FLS.FIELD_NUM = FL.FIELD_NUM AND FS.FARM_NUM = F.FARM_NUM AND CS.CLIENT_NUM = C.CLIENT_NUM) that it doesn't like. – Joe Oliphant Apr 02 '15 at 22:27
Are you working in SQL view? What happens when you build up these queries using Design View instead? (i.e. then you don't need to think about the parentheses) – Josh Miller Apr 03 '15 at 16:06

Join two queries in Access

2 Answers2