I have the following SQL query in MS Access. I'm trying to order the output by the results of an expression contained in the column labeled as 'Response'. My problem is that when I run the query Access prompts me to enter a parameter value for Response. I tried entering zero and one to see what would happen. The query runs in those cases but the sort order is wrong. Can someone please explain to me why this query would require a parameter? 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;
It is not requiring a parameter. The problem is that you are using columns aliases in one your Response
column. You need to use the actual calculations instead.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With