Count Null Values Per Field

[Can Of Worms]

Is there a way of having 1 query against 1 table that returns 1 row with the
same fields as the base table, and a Count of how many Null Values there are
in each field?

I am building a master attribute table for over 6,000 location, and am
getting that attribute information from all over the place within our company
in an attempt to have 1 master database that everyone can reference.

The problem is, no one table I have getting information from has all 6,000+
locations. They all have around 5,400-6,100 of the location, so my master
table has a fair number of null values, scattered among 51 columns

I am trying to be able to run 1 query that gives me how many pieces of
information are missing from each field. I know I can have 51 individual
queries, each one counting the number of null values, then do one query
joining on those 51 queries, but I have a gut feeling there HAS to be a
better way to do this.

Thanks.

 

[Jerry Whittle]

The main problem is that the table is not normalized properly. If you need to
look for the same sort of data across multiple fields, well, as you can see,
that's a problem.

You could do a total query something like below. The first field should be
on the primary key field for the table. If you don't have one, you could add
an autonumber field to the table and use it. The first field will show the
total number of records in the table.

The rest of the fields would count the number of records that have data in
the field excluding Nulls. Of course you statement will have a lot more
fields listed - about 52 of them.

SELECT DCount("[ID]","ASIF") AS Records,
Count(ASIF.OWNER) AS CountOfOWNER,
Count(ASIF.DATA_LENGTH) AS CountOfDATA_LENGTH,
Count(ASIF.DATA_PRECISION) AS CountOfDATA_PRECISION,
Count(ASIF.LAST_ANALYZED) AS CountOfLAST_ANALYZED,
Count(ASIF.SAMPLE_SIZE) AS CountOfSAMPLE_SIZE
FROM ASIF
GROUP BY DCount("[ID]","ASIF");

 

[Marshall Barton]

Is there a way of having 1 query against 1 table that returns 1 row with the
same fields as the base table, and a Count of how many Null Values there are
in each field?

I am building a master attribute table for over 6,000 location, and am
getting that attribute information from all over the place within our company
in an attempt to have 1 master database that everyone can reference.

The problem is, no one table I have getting information from has all 6,000+
locations. They all have around 5,400-6,100 of the location, so my master
table has a fair number of null values, scattered among 51 columns

I am trying to be able to run 1 query that gives me how many pieces of
information are missing from each field. I know I can have 51 individual
queries, each one counting the number of null values, then do one query
joining on those 51 queries, but I have a gut feeling there HAS to be a
better way to do this.

Better than 51 separate queries, but still rather tedious:

SELECT Count(*)-Count(fieldA) As fieldAnulls,
Count(*)-Count(fieldB) As fieldBnulls,
. . .
FROM [master table]