Access SQL join expression is not supported

Question

I'm using access to run a query but it is saying join operation is not supported. I've checked the syntax and it seems fine. Can anyone please help me understand why this is failing:

SELECT
   d.uid,
   md_20180901.monthval,
   md_20180801.monthval,
   md_20180701.val 
FROM
   (
(data as d 
      LEFT JOIN
         (
            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/09/2018# 
         )
         as md_20180901 
         ON d.UID = md_20180901.dataUID 
         AND md_20180901.monthval = #01/09/2018# ) 
      LEFT JOIN
         (
            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/08/2018# 
         )
         as md_20180801 
         ON d.UID = md_20180801.dataUID 
         AND md_20180801.monthval = #01/08/2018#
   )
   LEFT JOIN
      (
         SELECT
            md.* 
         FROM
            monthdata md 
         WHERE
            md.monthval = #01/07/2018# 
      )
      as md_20180701 
      ON d.UID = md_20180701.dataUID 
      AND md_20180701.monthval = #01/07/2018#
WHERE
   d.UID = "5B51141D";

For context, this is my tables

data table

ID
UID
MYNAME

monthdata table

ID
DATAUID
MONTHVAL
VAL

Answer 1

Start by removing all the unnecessary parts and making it simpler. Example:

            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/09/2018# 
         )
         as md_20180901 
         ON d.UID = md_20180901.dataUID 
         AND md_20180901.monthval = #01/09/2018#

can be simplified:

(SELECT val FROM monthdata WHERE monthval=#01/09/2018#) md_20180901
ON d.UID=md_20180901.dataUID

You do not need to repeat table names unless there is a field name conflict. You do not need to assign aliases that will never be used (in fact I generally only assign aliases if I am using the same table twice). You do not need to add monthval on filtering on the ON since you are already doing it in WHERE. The only field you are actually collecting is val (monthval is effectively a fixed value since you are filtering by a specific date) so don't use *.

Clean up your query and try adding one monthval at a time and see what happens.

Answer 2

Access only supports literals in join expressions when the expression has been surrounded by parentheses:

SELECT
   d.uid,
   md_20180901.monthval,
   md_20180801.monthval,
   md_20180701.val 
FROM
   (
(data as d 
      LEFT JOIN
         (
            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/09/2018# 
         )
         as md_20180901 
         ON (d.UID = md_20180901.dataUID 
         AND md_20180901.monthval = #01/09/2018#) ) 
      LEFT JOIN
         (
            SELECT
               md.* 
            FROM
               monthdata md 
            WHERE
               md.monthval = #01/08/2018# 
         )
         as md_20180801 
         ON (d.UID = md_20180801.dataUID 
         AND md_20180801.monthval = #01/08/2018#)
   )
   LEFT JOIN
      (
         SELECT
            md.* 
         FROM
            monthdata md 
         WHERE
            md.monthval = #01/07/2018# 
      )
      as md_20180701 
      ON (d.UID = md_20180701.dataUID 
      AND md_20180701.monthval = #01/07/2018#)
WHERE
   d.UID = "5B51141D";

However, you're doing something weird here. You're also comparing inside subqueries. Remove those:

SELECT
   d.uid,
   md_20180901.monthval,
   md_20180801.monthval,
   md_20180701.val 
FROM
(
(data as d 
      LEFT JOIN
         monthdata as md_20180901 
         ON (d.UID = md_20180901.dataUID 
         AND md_20180901.monthval = #01/09/2018#) ) 
      LEFT JOIN
         monthdata as md_20180801 
         ON (d.UID = md_20180801.dataUID 
         AND md_20180801.monthval = #01/08/2018#)
   )
   LEFT JOIN
      monthdata as md_20180701 
      ON (d.UID = md_20180701.dataUID 
      AND md_20180701.monthval = #01/07/2018#)
WHERE
   d.UID = "5B51141D";