Why is a parameter required for this request?

Question

I have the following SQL query in MS Access. I am trying to arrange the output according to the results of an expression contained in a column labeled “Answer”. My problem is that when I launch a query, Access prompts me to enter a parameter value for Response. I tried entering zero and one to see what happens. The query is executed in these cases, but the sort order is incorrect. Can someone please explain to me why a parameter is required for this request? Am I doing something wrong?

SELECT Market, Sum(Calls) AS SumOfCalls, Sum([A25-54 IMPs] * 1000) AS Impressions, Round(SumOfCalls/Impressions, 6) AS Response FROM DRTV_CentralOnly WHERE [Creative]<>'#N/A' GROUP BY Market ORDER BY Response Desc; 

Answer 1

No parameter required. The problem is that you are using column aliases in the same Response column. Instead, you need to use actual calculations.

 SELECT Market , Sum(Calls) AS SumOfCalls , Sum([A25-54 IMPs] * 1000) AS Impressions , Round(Sum(Calls)/Sum([A25-54 IMPs] * 1000), 6) AS Response FROM DRTV_CentralOnly WHERE [Creative]<>'#N/A' GROUP BY Market ORDER BY 4 Desc; 

Answer 2

@bluefeet said:

The problem is that you are using column aliases in the same Response column.

Actually, this is not a problem. MS Access does allow AS clauses ("column aliases") to be used in the way OP used them.

Rather, the problem is that MS Access does not resolve AS clauses in the ORDER BY .

It modifies the ORDER BY to use an ordinal position that corrects the query. Changes to the SELECT - it's a red herring!

The following should work:

 SELECT Market, Sum(Calls) AS SumOfCalls, Sum([A25-54 IMPs] * 1000) AS Impressions, Round(SumOfCalls/Impressions, 6) AS Response FROM DRTV_CentralOnly WHERE [Creative]<>'#N/A' GROUP BY Market ORDER BY 4 Desc;