How do I sort a subquery?

[Willniven]

I have created a query with subqueries in order to calculate the crowding in an emergency department and how many patients are awaiting admission
I now want to order the query in such a way that I can see when the most patients are in the department or what the maximum number of patients awaiting beds is but when I try to do this in the design view via the "sort" row, it returns an error
This is my query so far
SELECT T1.Patient_ID, T1.ED_Number,T1.ARRIVAL_DATETIME,

(SELECT count(*) FROM [Attendances fin] as T2 WHERET1.ARRIVAL_DATETIME>T2.ARRIVAL_DATETIME ANDT1.ARRIVAL_DATETIME<T2.DEPART_DATETIME) AS Crowding,



(SELECT count(*) FROM[Attendances fin] as T2 WHERE T1.ARRIVAL_DATETIME>T2.DEC_ADMIT_DATETIME ANDT1.ARRIVAL_DATETIME<T2.DEPART_DATETIME) AS AwaitingAdmit
FROM [Attendances fin] AS T1;

Has anyone got any smart solutions as to how I can now do this?
Regards
Will

[June7]

Why would you sort subquery? Each of the subqueries returns a single aggregate value. Why would you want these same values with every Patient record?

Did you mean you tried to add ORDER BY clause to the outer query? What is the error message?

[Willniven]

Dear June7
Thank you for your reply!
Yes, when I tried to add the order by to the outer query it returned an error, when I tried to add the order by to the query itself, it did not actually change on the datasheet view.
From the above SQL, I can see how many patients are in the department as every new attendance comes in, and how many patients are awaiting beds, but with 35 000 rows, it does not make for easy reading, it would be good to be able to calculate the average number of patients in the department and the average number of patients awaiting beds.
Any ideas?!
Regards

[June7]

What is the error message?

Did you save that query as an object or is it just SQL statement in RecordSource? Might have to save it as a query object then reference the query object in another query or SQL statement.

An expression with DCount() is an option but domain aggregates can be slow performers.