Access query count with criteria

Question

I have an query to filter result like image

I've tried to write query with DCOUNT but not work

SELECT LANG, Count(LANG) AS [TOTAL], 
DCount("[B]","TEST","[B]='B'") AS B, DCount("[C]","TEST","[C]='C'") AS C, DCount("[D]","TEST","[D]='D'") AS D
FROM TEST
GROUP BY LANG;

This query will return all column 'B','C','D' is 1

I want to count only for field if have value, if empty, just let it empty like picture How i can do that ?

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2020-05-18T06:44:57

You could try conditional aggregation using the SUM function with IIF:

SELECT
    COUNT(LANG) AS TOTAL,
    SUM(IIF([B] = 'B', 1, NULL)) AS B,
    SUM(IIF([C] = 'C', 1, NULL)) AS C,
    SUM(IIF([D] = 'D', 1, NULL)) AS D
FROM TEST
GROUP BY
    LANG;

The reason for using the SUM function rather than COUNT, is that the former does not ignore NULL values, in the event that every value in the sum be NULL. That is, should a given aggregation have no matches, the above would return NULL (empty) for the sum, whereas COUNT or DCOUNT (which internally uses COUNT) would actually return zero.

Access query count with criteria

1 Answers