Discussion Locked

left jion to subquery with aggregate

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

6 replies

by john_wills

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

by sl-campbell

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.

This discussion is closed to new replies.