sql - MS-Access -> SELECT AS + ORDER BY = error

Question

I'm trying to make a query to retrieve the region which got the most sales for sweet products. 'grupo_produto' is the product type, and 'regiao' is the region. So I got this query:

SELECT TOP 1 r.nm_regiao,  (SELECT COUNT(*)
        FROM Dw_Empresa
        WHERE grupo_produto='1' AND 
        cod_regiao = d.cod_regiao) as total 
FROM Dw_Empresa d
INNER JOIN tb_regiao r ON r.cod_regiao = d.cod_regiao ORDER BY total DESC

Then when i run the query, MS-Access asks for the "total" parameter. Why it doesn't consider the newly created 'column' I made in the select clause?

Thanks in advance!

Answer 1

Old Question I know, but it may help someone knowing than while you cant order by aliases, you can order by column index. For example, this will work without error :

SELECT 
 firstColumn,
 IIF(secondColumn = '', thirdColumn, secondColumn) As yourAlias
FROM
 yourTable
ORDER BY
 2 ASC

The results would then be ordered by the values found in the second column wich is the Alias "yourAlias".