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

Aliases are only usable in the query output. You can't use them in other parts of the query. Unfortunately, you'll have to copy and paste the entire subquery to make it work.

Answer 2

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".

Answer 3

You can do it like this

select * from(
  select a + b as c, * from table)
  order by c

Access has some differences compared to Sql Server.

Answer 4

Why it doesn't consider the newly created 'column' I made in the select clause?

Because Access (ACE/Jet) is not compliant with the SQL-92 Standard.

Consider this example, which is valid SQL-92:

SELECT a AS x, c - b AS y
  FROM MyTable
 ORDER
    BY x, y;

In fact, x and y the only valid elements in the ORDER BY clause because all others are out of scope (ordinal numbers of columns in the SELECT clause are valid though their use id deprecated).

However, Access chokes on the above syntax. The equivalent Access syntax is this:

SELECT a AS x, c - b AS y
  FROM MyTable
 ORDER
    BY a, c - b;

However, I understand from @Remou's comments that a subquery in the ORDER BY clause is invalid in Access.