Nesting multiple instances of one table

[MJSmit]

I have a query that contains records for each time a project (identified by JournalID) in a given period (e.g. DEC02, which has number 7 in a field called 'Sorting') reports on the progress of a given indicator (identified by IndType and ID). Similar indicators across different Projects share one Code.
For reasons I can't explain here, the only way to get a correct sum for all indicators that share one code is to use the Max function, giving the maximum value reported before or in a given period by each project. But I want to see progress over many periods (7,9,11 etc.). I have the following SQL statement that works, and I want to join it to itself on my field called Code:

SELECT Code, Sum(MaxOfIndValue) AS DEC02
FROM (SELECT qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, Max(qIndicatorProgress.IndValue) AS MaxOfIndValue, Code FROM qIndicatorProgress WHERE (((qIndicatorProgress.Sorting)<=7)) GROUP BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, qIndicatorProgress.Code ORDER BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID)
GROUP BY Code
HAVING (((Code)<>"00"));

and the version with the next period added that I came up with, but which doesn't work, is:

SELECT Code, Sum(MaxOfIndValue) AS DEC02
FROM (SELECT qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, Max(qIndicatorProgress.IndValue) AS MaxOfIndValue, Code FROM qIndicatorProgress WHERE (((qIndicatorProgress.Sorting)<=7)) GROUP BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, qIndicatorProgress.Code ORDER BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID)
INNER JOIN
(SELECT Code, Sum(MaxOfIndValue) AS JUN03
FROM (SELECT qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, Max(qIndicatorProgress.IndValue) AS MaxOfIndValue, Code FROM qIndicatorProgress WHERE (((qIndicatorProgress.Sorting)<=9)) GROUP BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, qIndicatorProgress.Code ORDER BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID))
GROUP BY Code HAVING (((Code)<>"00"));

and to that, I'd want to add more nested data from the same query, using probably a lot of brackets to join all SELECT recordsets.

Any suggestions what I'm doing wrong? Am I trying something impossible, or did I make a basic syntactic mistake?

 

[Golom]

Sub-Queries in a FROM clause MUST have an Alias and you require an ON clause for a JOIN.

SELECT Code, Sum(MaxOfIndValue) AS DEC02


FROM 

(SELECT qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, Max(qIndicatorProgress.IndValue) AS MaxOfIndValue, Code FROM qIndicatorProgress WHERE (((qIndicatorProgress.Sorting)<=7)) GROUP BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, qIndicatorProgress.Code ORDER BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID) [COLOR=red] As T1[/color]

INNER JOIN

(SELECT Code, Sum(MaxOfIndValue) AS JUN03
FROM (SELECT qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, Max(qIndicatorProgress.IndValue) AS MaxOfIndValue, Code FROM qIndicatorProgress WHERE (((qIndicatorProgress.Sorting)<=9)) GROUP BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID, qIndicatorProgress.Code ORDER BY qIndicatorProgress.JournalID, qIndicatorProgress.IndType, qIndicatorProgress.ID)) [COLOR=red] As T2[/color]

[COLOR=red]ON T1.JournalID = T2.JournalID[/color]

WHERE [Code] <> "00"

GROUP BY Code