Help with syntax

[GWB]

Hello - my question is why does this query run fine:

SELECT sum(AvgOfCurrentSteamFlow)
FROM [SELECT dbo_Boiler_Steam_Production.ReadingDate AS Expr1, Hour([ReadingTime]) AS Expr3, Avg(dbo_Boiler_Steam_Production.CurrentSteamFlow) AS AvgOfCurrentSteamFlow 
FROM dbo_Boiler_Steam_Production
GROUP BY dbo_Boiler_Steam_Production.ReadingDate, Hour([ReadingTime])
HAVING (((Year([ReadingDate]))=[Select Year]))]. AS [%$##@_Alias];

But this gives me a bracketing error:

 
SELECT sum(AvgOfCurrentSteamFlow)
FROM [SELECT dbo_PWL_Boiler_Steam_Production.ReadingDate AS Expr1, Hour([ReadingTime]) AS Expr3, Avg(dbo_PWL_Boiler_Steam_Production.CurrentSteamFlow) AS AvgOfCurrentSteamFlow 
FROM dbo_PWL_Boiler_Steam_Production
GROUP BY dbo_PWL_Boiler_Steam_Production.ReadingDate, Hour([ReadingTime])
HAVING (((Year([ReadingDate]))=[Select Year]))]. AS [%$##@_Alias];

Thanks for any help!

[ssanfu]

Actually, I couldn't get either one to execute.

I found that there as a dot (.) that shouldn't be there and the alias should be on the FROM clause in the subquery, not on the HAVING clause of the main query.


Here is the first query:

SELECT Sum(AvgOfCurrentSteamFlow) AS Expr2
FROM (SELECT ReadingDate AS Expr1, Hour([ReadingTime]) AS Expr3, Avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow 
FROM dbo_Boiler_Steam_Production AS [%$##@_Alias] 
GROUP BY ReadingDate, Hour([ReadingTime]))
WHERE (((Year([ReadingDate]))=[Select Year]));

Here is the second query:

SELECT Sum(AvgOfCurrentSteamFlow) AS Expr2
FROM (SELECT ReadingDate AS Expr1, Hour([ReadingTime]) AS Expr3, Avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow 
FROM dbo_PWL_Boiler_Steam_Production AS [%$##@_Alias] 
GROUP BY ReadingDate, Hour([ReadingTime]))
WHERE (((Year([ReadingDate]))=[Select Year]));

[GWB]

Thanks for that reply. I did try it and what happened is the query asked me to input a ReadingDate.... my original query just asks for a year. What I'm trying to do is get the total of all the averages. Strangely enough I did get it to work with this:

SELECT sum(AvgOfCurrentSteamFlow)
FROM [SELECT dbo_PWL_Boiler_Steam_Production.ReadingDate AS Expr1, Hour([ReadingTime]) AS Expr3, Avg(dbo_PWL_Boiler_Steam_Production.CurrentSteamFlow) AS AvgOfCurrentSteamFlow   
 FROM dbo_PWL_Boiler_Steam_Production
 GROUP BY dbo_PWL_Boiler_Steam_Production.ReadingDate, Hour([ReadingTime])
 HAVING (((Year([ReadingDate]))=[Select Year]))]. AS [%$##@_Alias];

I dont understand why but noticed that the code is shifted around by access and then works.

[weekend00]

Access changes "()" around a subquery to "[]." quietly, then tell you there is syntax error.

[GWB]

Access changes "()" around a subquery to "[]." quietly, then tell you there is syntax error.

Thanks for that -I thought I was going mad!
I'm going to start a new thread with a query built from scratch