SQL works, but won't save

T

Tim

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.
 
J

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.
 
T

Tim

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]);


Jeff L said:
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.
Click to expand...
Click to expand...
 
Top