Search code examples
sqlms-accesscrosstab

Display 'X' in crosstab output in place of count value

I have a crosstab query in MSAccess that displays the count of the occurrences of each column heading. I would like to substitute an 'X' in place of any integer.

I can change the word Count in both places of the TRANSFORM statement to First, and it will display the Column Heading in each row in place of the Count, but I cannot get it to insert the 'X' ...

Header looks like this: | Site ID | 737 | 747 | 757 | ...

Result looks like this: | Site 15 | 5 | 5 | 5 | ...

Desired result: | Site 15 | X | X | X | ...

SQL:

     TRANSFORM Count([tblPART-TO-AIRCRAFT].ACType) AS CountOfACType
     SELECT [tblPART-TO-AIRCRAFT].[SITE ID]
     FROM [tblPART-TO-AIRCRAFT]
     GROUP BY [tblPART-TO-AIRCRAFT].[SITE ID]
     PIVOT [tblPART-TO-AIRCRAFT].[ACType];
Solution

  • Consider:

    TRANSFORM IIf(Count(*) IS NULL, Null, "X") AS CountOfACType
SELECT [tblPART-TO-AIRCRAFT].[SITE ID]
FROM [tblPART-TO-AIRCRAFT]
GROUP BY [tblPART-TO-AIRCRAFT].[SITE ID]
PIVOT [tblPART-TO-AIRCRAFT].[ACType];