Query ORDER BY statement seems to have no effect.

[Stang70Fastback]

Hey guys, this is the query I am attempting to run:

SELECT Count(Query1.Route) AS NumberofReRoutes, Query1.Route
FROM Query1
WHERE [Start Date] <= #9/30/2014# AND [End Date] >= #9/1/2014#
GROUP BY Query1.Route
ORDER BY "NumberofReRoutes" DESC;

The last line seems to make absolutely no difference. I can change it to ASC, or I can remove the line entirely, and nothing changes. Can someone explain to me what I am missing? I need these sorted by the NumberofReRoutes field, but this method isn't working!

The output at the moment is two columns: Route, and NumberofReRoutes.

Thanks!

 

[boerbende]

I had the same problems when calculating another column for example test = 10 * NumberofReroutes. I think the query does not recognize your NumberofReroutes as name he can sort on.
try sort on Count(Query1.Route)

 

[Brianwarnock]

Boerbende is correct as you cannot order by on an alias

Brian

 

[jdraw]

I agree that Access sql doesn't recognize the alias name in the Order By; it repeats the calculation of the alias assignment in the order By. But,did you try it without the quotes around "NumberofReRoutes"? I'm just curious why you chose to put quotes on the alias name.