Access: Use scalar correlated subquery result for sorting

Question

In Access, is it possible to use the result of a scalar correlated subquery to sort the resultant rows? This didn't work (returned a sql error):

SELECT (SELECT MIN(DateTime)  FROM Appt WHERE  (PatientID = XXX.ID)) AS minDT, XXX.FullName, 
FROM Patient AS XXX
ORDER BY minDT;

Replacing the "minDT" in the ORDER BY clause with the entire expression didn't work either.

Answer 1

Access won't allow you to use a field expression alias in the ORDER BY, but you can use a column's ordinal number ...

SELECT *
FROM
    (
        SELECT
            (
                SELECT MIN([DateTime])
                FROM Appt
                WHERE (PatientID = XXX.ID)
            ) AS minDT,
            XXX.FullName
        FROM Patient AS XXX
    ) AS sub
ORDER BY 1;

Note I bracketed the DateTime field name because I suspect it's a reserved word.

And don't include a comma after the last item in the SELECT field expression list.