Q:

MS Access JOIN same table or query that allows comparison of data in same table

I am struggling with this syntax in MS Access 2013. I have tables that contain multiple years of data. I would like to write a query that compares data year to year. The below doesn't run and just generates a JOIN expression not supported.:
SELECT c.Year, c.Company, c.HavePlan, ly.HavePlan
FROM plan_data AS c
LEFT JOIN plan_data AS ly
ON c.Company = ly.Company AND ly.Year = 2014
WHERE c.Year = 2015
ORDER BY c.Company;

I have also tried using subqueries in the SELECT statement, but if I want to compare 50 fields of data, the code becomes very convoluted. For example, I have tried to replace ly.HavePlan with:
(SELECT HavePlan FROM plan_data WHERE Year = 2014) AS LyHavePlan

I made a little progress rewriting as the following:
SELECT DISTINCT c.Year, c.Company, c.[Plan #], c.HavePlan, ly.HavePlan
FROM plan_data AS c
LEFT JOIN plan_data AS ly
ON c.Company = ly.Company 
WHERE c.Year = 2015 AND ly.Year = 2014
ORDER BY c.Company;

However, this only gives me companies that were in both years and drops those there were not in 2014, which I don't want to do. I would like to compare both years, but keep the company for the most recent year. They just won't have anything to compare to last year.
It seems the last year data needs to be specified somewhere in the JOIN.

A:

You can avoid that "JOIN expression not supported" error by putting your ON conditions in parentheses.
I tested this query in Access 2010 ...
SELECT c.Year, c.Company, c.HavePlan, ly.HavePlan
FROM
    plan_data AS c
    LEFT JOIN plan_data AS ly
    ON (c.Company = ly.Company AND ly.Year = 2014)
WHERE c.Year = 2015
ORDER BY c.Company;

The Access query designer will complain it can't display that join in Design View.  But it worked fine when edited and tested from SQL View.
Another approach is to make ly a subquery and move the Year = 2014 constraint to the suquery's WHERE clause ... so it's not then needed in the join's ON clause ...
SELECT c.Year, c.Company, c.HavePlan, ly.HavePlan
FROM
    plan_data AS c
    LEFT JOIN
        (
            SELECT Company, HavePlan
            FROM plan_data
            WHERE [Year] = 2014
        ) AS ly
    ON c.Company = ly.Company
WHERE c.Year = 2015
ORDER BY c.Company;

