Embedding a subquery in a subquery

Question

I'm trying to combine these queries by embedding one in the other:

SELECT Fall.SamplePointID, SoilProperties.pH
FROM
(SELECT SamplePointID, Season, Nz (Diameter, 0) AS D 
FROM SeedlingStatus 
WHERE Season = "fall") AS Fall INNER JOIN SoilProperties ON Fall.SamplePointID = SoilProperties.SamplePointID;

SELECT Fall.SamplePointID
FROM (SELECT SamplePointID, Season, Nz (Diameter, 0) AS D 
FROM SeedlingStatus 
WHERE Season = "spring")  AS Spring INNER JOIN (SELECT SamplePointID, Season, Nz (Diameter, 0) AS D 
FROM SeedlingStatus 
WHERE Season = "fall")  AS Fall ON Spring.SamplePointID = Fall.SamplePointID
WHERE Fall.D> Spring.D;

I've tried combining them in ways like this, but then I get an error saying that it cannot find the table "Fall"

SELECT Fall.SamplePointID, SoilProperties.pH
FROM Fall INNER JOIN SoilProperties ON Fall.SamplePointID = SoilProperties.SamplePointID, (SELECT SamplePointID, Season, Nz (Diameter, 0) AS D 
FROM SeedlingStatus 
WHERE Season = "spring") AS Spring INNER JOIN (SELECT SamplePointID, Season, Nz (Diameter, 0) AS D 
FROM SeedlingStatus 
WHERE Season = "fall") AS Fall ON Spring.SamplePointID = Fall.SamplePointID
WHERE Fall.D> Spring.D;

Answer 1

Since the Fall derived table is the common data source, simply add Spring as a second INNER JOIN to first query. Remember too unlike other SQL dialects, MS Access requires parenthesis pairings with more than one join.

SELECT Fall.SamplePointID, sp.pH 
FROM ((
    SELECT SamplePointID, Season, Nz(Diameter, 0) AS D 
    FROM SeedlingStatus 
    WHERE Season = 'fall'
) AS Fall 
INNER JOIN SoilProperties sp
    ON Fall.SamplePointID = sp.SamplePointID)
INNER JOIN (
    SELECT SamplePointID, Season, Nz(Diameter, 0) AS D 
    FROM SeedlingStatus 
    WHERE Season = 'spring'
) AS Spring
    ON Fall.SamplePointID = Spring.SamplePointID
WHERE Fall.D > Spring.D

Hopefully, the Microsoft team will upgrade Access' current SQL dialect in future release including adding CTEs per my feedback ticket!