Cannot have aggregate function in order by clause

[nggman]

I have done some research on this but can't come up with a good solution. I have a crosstab query and I need to sort on an aggregated field. I need it to happen automatically (when the query runs) because I plan on making a macro.



Here is my SQL. The field in bold is the one I need to sort by. Since I can not do the sort with this query as is, can I somehow place this SQL code inside of another query and somehow get it sorted?


TRANSFORM Sum([dbo_RX Reporting].Price) AS SumOfPrice
SELECT [dbo_RX Reporting].[Master Drug Name], Sum([dbo_RX Reporting].Price) AS Total
FROM [dbo_RX Reporting]
WHERE ((([dbo_RX Reporting].[Therapeutic Class]) Like "*Analg*") AND (([dbo_RX Reporting].Period) Like "2018*") AND (([dbo_RX Reporting].[Plan Name])="Alabama"))
GROUP BY [dbo_RX Reporting].[Master Drug Name]
PIVOT [dbo_RX Reporting].Period;

[ranman256]

make a query, Q1, that does the summing,
then make a Crosstab of Q1