General discussion

Locked

left jion to subquery with aggregate

By john_wills ·
SELECT [%$##@_Alias].SessionDate, [%$##@_Alias].DayOfWeek, [%$##@_Alias].NumberOfClients
FROM [SELECT Appearance.SessionDate, Format([SessionDate],"dddd") AS DayOfWeek, Count(Appearance.BISNumber) AS NumberOfClients FROM Appearance GROUP BY Appearance.SessionDate]. AS [%$##@_Alias];

When I adapt the foregoing, which I have saved in a report, I produce the following, which does what I want but which I cannot save: I get the message “Syntax error in FROM clause”. The help reference is empty. When I copy the following into a stand-alone query I get the same message when trying to execute, the cursor then resting at the comma after the first parameter of Format. The same happens when I copy it directly into the SQL window of myreport query instead of constructing in Design View.

SELECT DateList.ThisDate, [%$##@_Alias].SessionDate, [%$##@_Alias].DayOfWeek, [%$##@_Alias].NumberOfClients
FROM DateList LEFT JOIN [SELECT Appearance.SessionDate, Format([SessionDate],"dddd")AS DayOfWeek, Count(Appearance.BISNumber) AS NumberOfClients FROM Appearance GROUP BY Appearance.SessionDate]. AS [%$##@_Alias] ON DateList.ThisDate = [%$##@_Alias].SessionDate;


Any idea what I should do next?

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

left jion to subquery with aggregate

by Bojidar Alexandrov In reply to left jion to subquery wit ...

Standart syntax for subquerys are in parenthes () ie try to put entire subquery in ().

Bojidar Alexandrov

Collapse -

left jion to subquery with aggregate

by john_wills In reply to left jion to subquery wit ...

Access has a limit, apparently undocumented, on nesting. The solution is to put the subqueries, or some of them, in separate query modules.

Collapse -

left jion to subquery with aggregate

by sl-campbell In reply to left jion to subquery wit ...

Why are you using an Alias table here? The problem is not the Format directly. I see several errors here.

The first SELECT could be written like this:

SELECT [%$##@_Alias].[SessionDate],Format([%$##@_Alias].[SessionDate],"dddd") AS DayOfWeek, Count([%$##@_Alias].BISNumber) AS NumberOfClients
FROM Appearance AS [%$##@_Alias]
GROUP BY [%$##@_Alias].SessionDate;

But, if you are trying to do what I think you are trying to do then I do then you probably do not need an Alias. Better would be (For the second SELECT):

SELECT DateList.ThisDate,
Format(DateList.ThisDate,"dddd") AS DayOfWeek,
Count(Appearance.BISNumber) AS NumberOfClients
FROM DateList
LEFT JOIN Appearance
ON DateList.ThisDate = Appearance.SessionDate
GROUP BY DateList.ThisDate;

If this is to be grouped by Clients then you will need to include that into the Group BY.

Collapse -

left jion to subquery with aggregate

by john_wills In reply to left jion to subquery wit ...

The error message goes away when I put the nested query in a separate query module.

Collapse -

left jion to subquery with aggregate

by john_wills In reply to left jion to subquery wit ...

The Access query builder turns my parentheses around a subquery into square brackets.

Collapse -

left jion to subquery with aggregate

by john_wills In reply to left jion to subquery wit ...

This question was closed by the author

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums