SQL works, but won't save

[Guest]

Hello,
I've built my query, and it works, but if I go into the SQL view it
generically informs me I have a "Syntax error in FROM clause" and sets the
cursor to the comma before "Max(Year)" in line 3. Any ideas what Access
doesn't like?

SELECT Q.[CountryID], Q.[Output], Q.Year
FROM [TableName] AS Q INNER JOIN
[SELECT [CountryID], Max(Year) As S
FROM (SELECT R.[CountryID], R.[Year], R.[Output]
FROM [TableName] as X INNER JOIN
[TableName] as R ON X.ID=R.ID WHERE R.[Output] Is Not Null)
GROUP BY [CountryID]]. AS T ON (Q.Year = T.S) AND
(Q.[CountryID]=T.[CountryID]);

Basically my goal is to return the most recent non-null entry in the output
column.

 

[Jeff L]

Year is a reserved word in Access. It is a function that is used to
extract the year from a date value. It would be good to change your
field name to something else. My guess is that it is causing an issue
in your query too.

 

[Guest]

Thanks for the catch, but it still gives the same error. SQL code as it
stands now:

SELECT Q.[CountryID], Q.[Output], Q.DataYear
FROM [TableName] AS Q INNER JOIN [SELECT [CountryID], Max(DataYear) As S
FROM (SELECT R.[CountryID], R.[DataYear], R.[Output]
FROM [TableName] as X INNER JOIN
[TableName] as R ON X.ID=R.ID WHERE R.[Output] Is Not Null)
GROUP BY [CountryID]]. AS T ON (Q.DataYear = T.S) AND
(Q.[CountryID]=T.[CountryID]);

Year is a reserved word in Access. It is a function that is used to
extract the year from a date value. It would be good to change your
field name to something else. My guess is that it is causing an issue
in your query too.

Hello,
I've built my query, and it works, but if I go into the SQL view it
generically informs me I have a "Syntax error in FROM clause" and sets the
cursor to the comma before "Max(Year)" in line 3. Any ideas what Access
doesn't like?

SELECT Q.[CountryID], Q.[Output], Q.Year
FROM [TableName] AS Q INNER JOIN
[SELECT [CountryID], Max(Year) As S
FROM (SELECT R.[CountryID], R.[Year], R.[Output]
FROM [TableName] as X INNER JOIN
[TableName] as R ON X.ID=R.ID WHERE R.[Output] Is Not Null)
GROUP BY [CountryID]]. AS T ON (Q.Year = T.S) AND
(Q.[CountryID]=T.[CountryID]);

Basically my goal is to return the most recent non-null entry in the output
column.

 

[Gary Walter]

You just cannot use *any further brackets*
in a subquery that is used in the FROM
clause.

Why? Because even though you wrote it
out surrounding the subquery in parenthesis,
Access wants to change it to form that actually
surrounds with brackets and ends in a period.
And within that bracketed subquery, it chokes
on any further brackets....

that precludes using an inner subquery in a FROM
clause WITHIN an outer subquery used in a FROM
clause...

One possible solution (assuming "TableName"
does not contain space(s) so does not need
bracketing)....

you will need to save the "inner" as a query
(say "qryInner")

SELECT
R.CountryID,
R.DataYear,
R.Output
FROM
TableName as X
INNER JOIN
TableName as R
ON X.ID=R.ID
WHERE R.Output Is Not Null;

SELECT
Q.CountryID,
Q.Output,
Q.DataYear
FROM
TableName AS Q
INNER JOIN
[SELECT
CountryID,
Max(DataYear) As S
FROM qryInner
GROUP BY
CountryID]. AS T
ON
(Q.DataYear = T.S)
AND
(Q.[CountryID]=T.[CountryID]);

------------
or "qryMaxDataYear"

SELECT
R.CountryID,
Max(R.DataYear) As S,
FROM
TableName as X
INNER JOIN
TableName as R
ON X.ID=R.ID
WHERE R.Output Is Not Null
GROUP BY
R.CountryID;


SELECT
Q.CountryID,
Q.Output,
Q.DataYear
FROM
TableName AS Q
INNER JOIN
qryMaxDataYear As T
ON
(Q.DataYear = T.S)
AND
(Q.[CountryID]=T.[CountryID]);

-----------
or
(I don't know your data
so this may be inappropriate,
but it looks like you are "saying"):

SELECT
Q.CountryID,
Q.Output,
Q.DataYear
FROM
TableName AS Q
WHERE
Q.DataYear =
(SELECT
Max(t.DataYear)
FROM
TableName As t
WHERE
t.CountryID = Q.CountryID);

Thanks for the catch, but it still gives the same error. SQL code as it
stands now:

SELECT Q.[CountryID], Q.[Output], Q.DataYear
FROM [TableName] AS Q INNER JOIN [SELECT [CountryID], Max(DataYear) As S
FROM (SELECT R.[CountryID], R.[DataYear], R.[Output]
FROM [TableName] as X INNER JOIN
[TableName] as R ON X.ID=R.ID WHERE R.[Output] Is Not Null)
GROUP BY [CountryID]]. AS T ON (Q.DataYear = T.S) AND
(Q.[CountryID]=T.[CountryID]);

Year is a reserved word in Access. It is a function that is used to
extract the year from a date value. It would be good to change your
field name to something else. My guess is that it is causing an issue
in your query too.

Hello,
I've built my query, and it works, but if I go into the SQL view it
generically informs me I have a "Syntax error in FROM clause" and sets
the
cursor to the comma before "Max(Year)" in line 3. Any ideas what Access
doesn't like?

SELECT Q.[CountryID], Q.[Output], Q.Year
FROM [TableName] AS Q INNER JOIN
[SELECT [CountryID], Max(Year) As S
FROM (SELECT R.[CountryID], R.[Year], R.[Output]
FROM [TableName] as X INNER JOIN
[TableName] as R ON X.ID=R.ID WHERE R.[Output] Is Not
Null)
GROUP BY [CountryID]]. AS T ON (Q.Year = T.S) AND
(Q.[CountryID]=T.[CountryID]);

Basically my goal is to return the most recent non-null entry in the
output
column.

 

[Gary Walter]

:

SELECT Q.[CountryID], Q.[Output], Q.Year
FROM [TableName] AS Q INNER JOIN
[SELECT [CountryID], Max(Year) As S
FROM (SELECT R.[CountryID], R.[Year], R.[Output]
FROM [TableName] as X INNER JOIN
[TableName] as R ON X.ID=R.ID WHERE R.[Output] Is Not Null)
GROUP BY [CountryID]]. AS T ON (Q.Year = T.S) AND
(Q.[CountryID]=T.[CountryID]);

Basically my goal is to return the most recent non-null entry in the
output
column.

sorry, just read final sentence....

SELECT
Q.CountryID,
Q.Output,
Q.DataYear
FROM
TableName AS Q
WHERE
Q.DataYear =
(SELECT
Max(t.DataYear)
FROM
TableName As t
WHERE
t.Output IS NOT NULL
AND
t.CountryID = Q.CountryID);