Access SQL: subquery into IIF?

Question

I have the following query that works fine:

SELECT NomComplet, IIF(Count(FS3.Index) = 0, '0 (RAS)', Count(FS3.Index))  
 FROM ControleAcces INNER JOIN ( 
     Employes LEFT JOIN ( 
         SELECT FS1.Index, FS1.OTP, FS1.OTP, FS1.Axe, FS1.FaitSaillant, FS1.Utilisateur, FS2.DateInsertion  
         FROM FaitsSaillants AS FS1 INNER JOIN ( 
             SELECT Axe, Index, Max(FaitsSaillants.DateInsertion) AS DateInsertion  
             FROM FaitsSaillants  
             WHERE DateValue(DateInsertion) > #2010-01-01#  
             AND DateValue(DateInsertion) < #2011-12-31#  
             GROUP BY Axe, Index 
         ) AS FS2  
         ON (FS1.DateInsertion = FS2.DateInsertion  
         AND FS1.Index = FS2.Index)  
         WHERE FS1.Axe = 'Project' AND FS2.Axe = 'Project' 
     ) AS FS3  
     ON Employes.CIP = FS3.Utilisateur 
 )  
 ON ControleAcces.Valeur = Employes.CIP
 GROUP BY NomComplet
 ORDER BY NomComplet

Don't bother to fully understand it, all I want it to edit my IIF condition on the first line. Actually, the condition doesn't do much, it checks how many FS3.Index the query returns and concatenate (RAS) if it's 0. However, in fact, I would like it to check if there is any row in FaitsSaillants where Axe = 'RAS'. If the Count() of this is > 0, then the condition is met.

Can I do a subquery into the IIF segment, something like SELECT COUNT(Index) FROM FaitsSaillants WHERE Axe = 'RAS' AND Utilisateur = FS1.Utilisateur? If the result is 0, then I add the RAS to my second field's results. If not, it stays Count(FS3.Index).

I tried it and while the syntax is correct, the problem is it can't check for the Utilisateur = FS1.Utilisateur condition because FS1 is in the main query. However, I must check this because this is the only way to be sure that I'm looking for the right thing: it must be the same Utilisateur whether I'm in the main query or the subquery.

EDIT:

Here is a shorter version of what I tried from the answers/comments below.

SELECT NomComplet, IIf(FS2.AxeCount > 0, "0 (RAS)", count(FS3.index))
FROM ControleAcces INNER JOIN (Employes LEFT JOIN (SELECT FS2.AxeCount, FS1.Index, FS1.OTP, FS1.OTP, FS1.Axe, FS1.FaitSaillant, FS1.Utilisateur, FS2.DateInsertion  
         FROM FaitsSaillants AS FS1 INNER JOIN ( 
             SELECT Axe, Index, Max(FaitsSaillants.DateInsertion) AS DateInsertion, SUM(IIf(Axe = 'RAS', 1, 0)) As AxeCount  
             FROM FaitsSaillants 
             GROUP BY Axe, Index 
         ) AS FS2  
         ON (FS1.DateInsertion = FS2.DateInsertion  
         AND FS1.Index = FS2.Index)  
     )  AS FS3 ON Employes.CIP = FS3.Utilisateur) ON ControleAcces.Valeur = Employes.CIP
GROUP BY NomComplet;

I still got an error about FS2.AxeCount that isn't a part of the aggregate function (iff).

I've also tried this:

SELECT NomComplet, IIf((select count(*) from FaitsSaillants where axe='RAS' and Utilisateur=ControleAcces.Valeur) > 0, "0 (RAS)", count(FS3.index))
FROM ControleAcces INNER JOIN (Employes LEFT JOIN (SELECT FS2.AxeCount, FS1.Index, FS1.OTP, FS1.OTP, FS1.Axe, FS1.FaitSaillant, FS1.Utilisateur, FS2.DateInsertion  
         FROM FaitsSaillants AS FS1 INNER JOIN ( 
             SELECT Axe, Index, Max(FaitsSaillants.DateInsertion) AS DateInsertion, SUM(IIf(Axe = 'RAS', 1, 0)) As AxeCount  
             FROM FaitsSaillants 
             GROUP BY Axe, Index 
         ) AS FS2  
         ON (FS1.DateInsertion = FS2.DateInsertion  
         AND FS1.Index = FS2.Index)  
     )  AS FS3 ON Employes.CIP = FS3.Utilisateur) ON ControleAcces.Valeur = Employes.CIP
GROUP BY NomComplet, ControleAccess.Valeur;

Olivier Jacot-Descombes · Accepted Answer

FS3.Index is NULL if there is no corresponding record, because of the LEFT JOIN. Wouldn't a test

IIf(IsNull(FS3.Index), ..., ...)

... be sufficient? I'm not sure so, since other conditions and joins are involved as well.

UPDATE (recaptulation of comments)

We can get the desired count (AxeCount) from the innermost nested SELECT (FS2):

SELECT
    Axe, Index, Max(FaitsSaillants.DateInsertion) AS DateInsertion,
    SUM(IIf(Axe = 'RAS', 1, 0)) As AxeCount
FROM FaitsSaillants  
...

This intermediate result must be passed to the outermost SELECT by including it in the select list of the intermediate SELECT (FS3):

SELECT FS2.AxeCount, FS1.Index, ...

The outer most SELECT has a GROUP BY clause. In this case, all fields of the select list must either be included in the GROUP BY clause or must be included in an aggregate function. The GROUP BY clause groups rows by the fields listed in this very clause. This usually reduces the number of rows, as several rows similar in their group fields are condensed to form one row. This means that the values of the remaining fields of the select list (not in the group fields) must be combined together. This is what the aggregate function does. Aggregate functions are

Avg (average)
Count
First, Last
Min, Max (minimum, maximum)
StDev, StDevP (standard deviation)
Sum
Var, VarP (variance)

See SQL Aggregate Functions (Access)

Now, we can add this in the outermost select list

IIf(SUM(FS2.AxeCount) > 0, ..., ...)

Access SQL: subquery into IIF?

Answers (2)

Related Questions