How to order a recordest returned from a query by a field created within that same query (MS ACCESS)

The Query (For this question I do not think that you need to see schema):

SELECT Agencies.AgencyName, (SELECT DISTINCT MAX(Invoices.CostsTotal) FROM Invoices WHERE Contracts.ContractID = Invoices.ContractID) AS MaxInvoice
FROM Contracts 
LEFT JOIN Agencies ON Contracts.AgencyID = Agencies.AgencyID
ORDER BY MaxInvoice DESC;

How do we order the recordset returned from a query by a field created within that same query?

I have seen the function FIELDS(INDEX) ? But this does not exist in access? Also not sure that it would even work. In this instance I want to sort the recordset by the MaxInvoice field.

MS Access prompts me to enter a parameter value for MaxInvoice when I attempt to run this query

Answer

You can write parent SELECT which wraps your current SELECT. Like this:

SELECT * FROM (
 SELECT Agencies.AgencyName, 
 (SELECT DISTINCT MAX(Invoices.CostsTotal) FROM Invoices 
 WHERE Contracts.ContractID = Invoices.ContractID) AS MaxInvoice
 FROM Contracts LEFT JOIN Agencies 
 ON Contracts.AgencyID = Agencies.AgencyID
) AS ContractsLargestInvoice
ORDER BY ContractsLargestInvoice.MaxInvoice DESC;