Crosstab IIF Help

[eerich]

Hi Everyone,

I'm struggling to get correct results from a crosstab query with an Inline IF statement. I'm going through a security data file and would like to have results mark 'r' or 'w' based on the security settings. so i have the following code:

TRANSFORM First(IIf(((SECCode ='A'
                      AND F.Run='Y'
                      AND F2.SECCode='P'
                      AND F2.FLAG='U')),'w','r')) AS SecValue
SELECT F.Program,
       F.Description
FROM (FNDLIST AS F
        INNER JOIN FNDLIST AS F2 ON (F.Program =F2.Program
                                    AND F.User=F2.User)
 GROUP BY F.Program,
         F.Description
ORDER BY 1
PIVOT F.User;

My results are inconsistent. Sometimes it pulls the right value, other times it will have a 'r' when it should be 'w'. I think the issue lies within the aggregate (FIRST) but can't be certain. Is there any other non-mathematical based aggregate that can be used? I tried to just use 'VAL' and received an error.

Any help is appreciated.

 

[dhookom]

Min or Max might be more consistent.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Like Duane said Min and Max will be more consistent.

But my guess is what your really want is to use aggregates on each of those internal values to the IIF rather than wrapping the whole thing in one aggregate.

In any case without understanding the tables and how the thing is supposed to work, we really can't say much else.