ORDER BY clause with Alias

Question

I am trying to get the sum of the market values for each given CPTY. Each CPTY can have several market values. In my output list, i want to see the top 5 CPTYs, ordered by the sum of their market values.

Works:

SELECT TOP 5 CPTY, SUM( MKT_VAL ) AS MarketVal
FROM Eod
GROUP BY CPTY
ORDER BY SUM( MKT_VAL ) DESC;

Fails:

SELECT TOP 5 CPTY, SUM( MKT_VAL ) AS MarketVal
FROM Eod
GROUP BY CPTY
ORDER BY MarketVal DESC;

It appears when I try to ORDER BY the Alias (MarketVal), Access asks for MarketVal parameter value. However, when I ORDER BY SUM( MKT_VAL ), the code executes fine. I can't understand why this is, can anyone tell me why?

Answer 1

There is no issue in both the query they will run properly.

you must inspect any expressions Or find the identifier that is causing this.