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.