Access does not summarize null values of crosstab query row headings

Question

I think this should be simple but I have no found a solution so far. I have a table V_POLE_FR., which has a structure like this (it has more fields)

ROWID (integer pk)
PROPRIETAIRE (string)
STATUS_FINAL (string)

I would like to summarize the number of poles by PROPRIETAIRE (the crosstab row heading), and get the numbers of each STATUS_FINAL (the crosstab query column heading). I get OK results for all the owners (PROPRIETAIRE) but there are some null values of owner and I get a blank row but with 0 count. How can I get to sum up also the poles having null owner? I have tried Nz but maybe not doing it right.

Here is my crosstab query:

TRANSFORM Count(V_POLE_FR.PROPRIETAIRE) AS [COUNT]
SELECT V_POLE_FR.PROPRIETAIRE, Count(V_POLE_FR.PROPRIETAIRE) AS TOTAL_PROPRIETAIRE
FROM V_POLE_FR
GROUP BY V_POLE_FR.PROPRIETAIRE
PIVOT V_POLE_FR.STATUS_FINAL;

And i get results like this (count of zero for nulls which should be 4):

PROPRIETAIRE TOTAL_PROPRIETAIRE   <>   A   B   C   D   E
(null)              0              0
X                   1                      1
Y                   81                         75   2
Z                   165                   165

The interesting thing is if I do a simple group by query, I get the totals right but I loose the separation by status:

SELECT DISTINCT V_POLE_FR.PROPRIETAIRE, COUNT(*) AS TOTAL
FROM V_POLE_FR
GROUP BY  V_POLE_FR.PROPRIETAIRE;

I thank your help. By the way I looked at several answers and any seemed to be this case?? I tried the Nz function it did not work.

You would get more response if you declare your database system and format your code. — Erwin Brandstetter

Patrick Honorez Patrick Honorez · Accepted Answer · 2014-08-04T14:23:08

0

votes

Try using Count(1) AS TOTAL_PROPRIETAIRE
By definition, Count ignores Null values.

Access does not summarize null values of crosstab query row headings

1 Answers