crosstab

[Jason]

Hi all

I have a crosstab query, that does what it should. However, I would like to
amend it slightly, so that if a the count for a column is not null, replce
the numeric value with a letter.

Is this possible?

 

[Duane Hookom]

This is possible using IIf() of Nz() in your Value expression. Could you
share your SQL view?

Also, I thought you would want to display a letter if the value was Null,
not the other way around. Please confirm your intention.

Your SQL based on your question might look like:

TRANSFORM IIf(Nz(Count([usiUsrLogin]),0)<>0,"A",Null) AS Expr1
SELECT tblUserSites.usiSitID
FROM tblUserSites
GROUP BY tblUserSites.usiSitID
PIVOT tblUserSites.usiRolID;