MS Access SQL - Problem with subquery and function in ORDER BY clause

Question

The senario is that some people apply for some positions.
So there are tApplicant, tPosition and a join table tPreferences for a many-to-many relationship between them.

I need to build a SQL expression where these should happen:

1. get some fields from a join of tApplicant and tPosition into a new table.
2. create a new field called AM which should be either 1 or 0.
   1 = If tApplicant.applicationID is found in a third not relevant table called tInfo.
   0 = If tApplicant.applicationID is not found there.
3. ORDER BY AM.

It is executed in VBA. This is what I 've got so far:

sSQL = "SELECT tApplicant.applicationID, tApplicant.name, tApplicant.ID, tPreferences.fld3, " & _
       "NZ((SELECT 1 FROM tInfo WHERE tInfo.applicationID = tApplicant.applicationID), 0) AS AM " _
       "INTO " & sTable & " FROM tPreferences INNER JOIN tApplicant " & _
       "ON tPreferences.IDapplic = tApplicant.applicationID " & _
       "WHERE tPreferences.IDposit = " & rsRos!ID & ";"
CurrentDB.Execute sSQL, dbFailOnError

It seems like step 3 cannot be done.
Adding ORDER BY AM, throws Run-time error '3061'. Too few parameters. Expected 1..
While adding ORDER BY NZ((SELECT 1 FROM tInfo WHERE tInfo.applicationID = tApplicant.applicationID), 0), throws Run-Time Error 3075: Syntax Error in Query Expression 'NZ((SELECT 1 FROM tAMEA WHERE tAMEA.aitisiID = tAiton.aitisiID), 0'..
If omitted, everything works fine but there's no ORDER BY.
How can I achieve this?
PS: If values 1 and 0 for AM make things complicated, and some other values instead could be easier to get with the query, it will be OK, I will deal with this in the rest of the code.

Answer 1

@ErikA and @Gustav 's answers pointed me to the right direction. Thank you both for your time.

The problem in this case was that I tried to use a subquery in the ORDER BY clause. Which I found out now that is not allowed. eg. see here
More over, I found this question, which makes mine a possible duplicate. Here it is suggested to wrap the query.
So I firstly SELECT the data in no order with the subquery and then, INSERT INTO the new table using ORDER BY with the new column of the subquery.

So I'm posting what finally worked for me.

sSQL = "SELECT * INTO " & sTable & " FROM (" & _
       "SELECT tApplicant.applicationID, tApplicant.name, tApplicant.ID, tProtimisi.fld3, " & _
       "NZ((SELECT 1 FROM tInfo WHERE tInfo.applicationID = tApplicant.applicationID), 0) AS AM " & _
       "FROM tPreferences INNER JOIN tApplicant " & _
       "ON tPreferences.IDapplic = tApplicant.applicationID " & _
       "WHERE tPreferences.IDposit = " & rsRos!ID & ") " & _
       "ORDER BY AM DESC;"
CurrentDb.Execute sSQL, dbFailOnError