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.

CodePudding user response：

Nz is an application-level function (technically, a method of the Access.Application object exposed as a function. It is unavailable for use in DAO.

CurrentDb.Execute is using DAO.

There are two rewrite possibilities:

1. Rewrite to avoid Nz:

   IIF((SELECT 1 FROM tInfo WHERE tInfo.applicationID = tApplicant.applicationID) IS NOT NULL, 1, 0)
   

   Or my preferred rewrite:

   ORDER BY EXISTS(SELECT 1 FROM tInfo WHERE tInfo.applicationID = tApplicant.applicationID) DESC
   

2. Rewrite to DoCmd.RunSQL which does allow these functions (and suppress warnings as desired:

   DoCmd.RunSQL sSQL
   

CodePudding user response：

First, Access might think that the subquery may return more than one record, thus Top 1 should be used.

Next, it makes no sense to order the records to be inserted, as records per definition carries no order unless you specify this in the target table or the query where that table is used as source.

Also, even if you insisted to sort the insert, Access can't do this, as it doesn't know the output for AM.

Thus, this will run:

sSQL = "SELECT tApplicant.applicationID, tApplicant.name, tApplicant.ID, tPreferences.fld3, " & _
       "NZ((SELECT Top 1 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

That said, Erik's suggestion to replace Nz with Exists is preferable as this will result in "clean SQL" which always will run faster:

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

Neither will this be sortable on AM. Also, it will in Access SQL return -1 and 0 for AM. To obtain 1, apply ABS:

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

CodePudding user response：

@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